<!DOCTYPE html>
<html lang="en" class="svg no-js">
<head>
    <meta charset="utf-8" />
    <!--[if IE ]>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1" />
    <![endif]-->
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="Language" content="en">

    <title>MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Syntax</title>
    <link rel="stylesheet" media="all" href="css/main-20190125.min.css" />
            <link rel="stylesheet" media="all" href="css/docs-20190125.min.css" />
    
    
    <link rel="stylesheet" media="print" href="css/print-20190125.min.css" />

    
    
    
    
            <link rel="contents" href="index.html" title="MySQL 8.0 Reference Manual" />
<link rel="start" href="index.html" title="MySQL 8.0 Reference Manual" />
<link rel="prev" href="create-spatial-reference-system.html" title="13.1.19 CREATE SPATIAL REFERENCE SYSTEM Syntax" />
<link rel="next" href="create-table-statement-retention.html?ff=nopfpls" title="13.1.20.1 CREATE TABLE Statement Retention" />
<link rel="up" href="sql-syntax-data-definition.html" title="13.1 Data Definition Statements" />

    
    <link rel="shortcut icon" href="favicon.ico" />
    <script>(function(H){ H.className=H.className.replace(/\bno-js\b/,'js') })(document.documentElement)</script>
    <script src="js/site-20181120.min.js"></script>

    
    
    
    <!--[if lt IE 9]>
        <script src="https://labs.mysql.com/common/js/polyfills/html5shiv-printshiv-3.7.2.min.js"></script>
        <script src="https://labs.mysql.com/common/js/polyfills/respond-1.4.2.min.js"></script>
    <![endif]-->
        <!--[if IE 9]>
        <style>#docs-sidebar-toc { box-sizing: content-box; }</style>
    <![endif]-->
    </head>

<body class="no-sidebar full-page dev">
<div class="page-wrapper">
    <header>

                        <a href="https://dev.mysql.com/" aria-label="Home" title="MySQL" id="l1-home-link"></a>
        
        <div id="l1-nav-container">
            <div id="l1-line1">
                <div id="l1-auth-links">
                    <a href="https://www.mysql.com/about/contact/"><b>Contact MySQL</b></a>
                    <span id="l1-contact-separator">&nbsp;|&nbsp;</span>
                    <span id="l1-contact-separator-br"><br /></span>
                                             <a href="https://dev.mysql.com/auth/login/?dest=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fcreate-table.html">Login</a> &nbsp;|&nbsp;
                         <a href="https://dev.mysql.com/auth/register/">Register</a>
                                    </div>
                <div id="l1-lhs">
                    <div id="l1-slogan">
                        The world's most popular open source database
                    </div>
                    <div id="l1-search-box">
                        <form id="l1-search-form" method="get" action="https://www.oracle.com/search/results">
                        <input type="hidden" name="cat" value="mysql" />
                        <input type="hidden" name="Ntk" value="SI-ALL5" />
                        <input id="l1-search-input" type="search" class="icon-search" placeholder="Search" aria-label="Search" name="Ntt" />
                        </form>
                    </div>
                </div>
            </div>
            <div id="l1-line2">
                <div class="social-icons">
                    <a aria-label="Join us on Facebook" title="Join us on Facebook" href="http://www.facebook.com/mysql"><span class="icon-facebook"></span></a>
                    <a aria-label="Follow us on Twitter" title="Follow us on Twitter" href="https://twitter.com/mysql"><span class="icon-twitter"></span></a>
                    <a aria-label="Follow us on LinkedIn" title="Follow us on LinkedIn" href="https://www.linkedin.com/company/mysql"><span class="icon-linkedin"></span></a>
                    <a aria-label="Visit our YouTube channel" title="Visit our YouTube channel" href="http://www.youtube.com/mysqlchannel"><span class="icon-youtube"></span></a>
                </div>
                <ul id="l1-nav">
                    <li>
                        <a href="https://www.mysql.com/"><!-- <span class="icon-sakila"></span>  -->MySQL.com</a>
                    </li><li>
                        <a href="https://www.mysql.com/downloads/"><!-- <span class="icon-download-thin"></span>  -->Downloads</a>
                    </li><li class="active">
                        <a href="/doc/"><!-- <span class="icon-books"></span>  -->Documentation</a>
                    </li><li>
                        <a href="/"><!-- <span class="icon-code"></span>  -->Developer Zone</a>
                    </li>                </ul>
            </div>
        </div>
        <div id="sub-header">
            <div id="l2-nav-container">
                <div id="l2-nav-toggle">
                    <span class="icon-three-bars"></span>
                </div>
                                                                                        <a class="button nav-button-3" href="/"><span class="icon-code"></span> Developer Zone</a>
                                                                                                                                    <a class="button nav-button-2" href="https://www.mysql.com/downloads/"><span class="icon-download-thin"></span> Downloads</a>
                                                                                                <a class="button nav-button-1" href="https://www.mysql.com/"><span class="icon-sakila"></span> MySQL.com</a>
                                                                        <div id="l2-search-toggle">
                    <span class="icon-search"></span>
                </div>
                <div id="l2-site-icon">
                                                                                                                                                                                                    <span class="icon-books"></span>
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        </div>
                <div id="l1-section-title">
                    <span id="l1-section-label">
                        <a href="/doc/">Documentation</a>
                    </span>
                </div>
                <nav>
                    

<ul id="l2-nav">
            	<li class="active"><a class="active " href="/doc/refman/en/">MySQL Server</a>
    			</li>
	        	<li class=""><a  href="/doc/index-enterprise.html">MySQL Enterprise</a>
    			</li>
	        	<li class=""><a  href="/doc/workbench/en/">Workbench</a>
    			</li>
	        	<li class=""><a  href="/doc/en/mysql-innodb-cluster-userguide.html">InnoDB Cluster</a>
    			</li>
	        	<li class=""><a  href="/doc/index-cluster.html">MySQL NDB Cluster</a>
    			</li>
	        	<li class=""><a  href="/doc/index-connectors.html">Connectors</a>
    			</li>
	        	<li class=" last"><a  href="/doc/index-other.html">More</a>
    			</li>
	                        <li class="other-section"><a href="https://www.mysql.com/">MySQL.com</a></li>
                            <li class="other-section"><a href="https://www.mysql.com/downloads/">Downloads</a></li>
                                        <li class="other-section"><a href="/">Developer Zone</a></li>
            </ul>
                </nav>
            </div>
            <div id="l3-search-container"></div>
            <div id="top-orange"><span id="section-nav">Section Menu: &nbsp; </span></div>
        </div>
    </header>
        <div id="page">
                        <div role="main" id="main">

            
                
            
<div>
    <div id="docs-sidebar-toc" class="">
    <div id="docs-toc-inner">
        <div class="docs-sidebar-header" id="docs-nav-header">
            <a class="docs-show-hide-nav" id="docs-hide-nav" href="" title="Hide Sidebar" aria-label="Hide Sidebar"><span class="icon-arrow-small-left"></span></a>
            <div class="docs-nav-links">
                                    
    <a href="create-spatial-reference-system.html"
        aria-label="Previous" title="Previous: CREATE SPATIAL REFERENCE SYSTEM Syntax"><span
        class="icon-chevron-left"></span></a>
<a href="index.html" aria-label="Start" title="Start"><span class="icon-book-open"></span></a>
        <a aria-label="Up" href="sql-syntax-data-definition.html" title="Up: Data Definition Statements"><span class="icon-chevron-up"></span></a>
    <a href="create-table-statement-retention.html" aria-label="Next"
        title="Next: CREATE TABLE Statement Retention"><span
        class="icon-chevron-right"></span></a>
                            </div>
        </div>
                    
<div id="docs-sidebar-search-container">
    <div id="docs-sidebar-search-box">
        <form method="get" action="/mysql-manual/search-page">
            <input type="hidden" name="d" id="d" value="201" />
            <input type="hidden" name="p" id="p" value="1" />
            <input type="text" name="keyword" id="q" title="Search this Manual"
                value=""
                style="color: #bbb;"
                onfocus=""
                onblur="" />

            <button class="docs-sidebar-search-btn" aria-label="Search" title="Search" type="submit">
                <span class="icon-search"></span>
            </button>
        </form>
    </div>
</div>
                <div class="docs-sidebar-nav">
            <a class="docs-icon-home" href="/doc/"><span class="icon-home"></span>Documentation Home</a><hr />
            <div class="docs-sidebar-mtitle">MySQL 8.0 Reference Manual</div>
            <nav class="doctoc" id="doc-201">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="preface.html">Preface and Legal Notices</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="introduction.html">General Information</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="installing.html">Installing and Upgrading MySQL</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="tutorial.html">Tutorial</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="programs.html">MySQL Programs</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="server-administration.html">MySQL Server Administration</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="security.html">Security</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="backup-and-recovery.html">Backup and Recovery</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="optimization.html">Optimization</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="language-structure.html">Language Structure</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="charset.html">Character Sets, Collations, Unicode</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="data-types.html">Data Types</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="functions.html">Functions and Operators</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-down"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax.html">SQL Statement Syntax</a></div></div>                        <div class="docs-submenu">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-down"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-data-definition.html">Data Definition Statements</a></div></div>                        <div class="docs-submenu">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="atomic-ddl.html">Atomic Data Definition Statement Support</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-database.html">ALTER DATABASE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-event.html">ALTER EVENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-function.html">ALTER FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-instance.html">ALTER INSTANCE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-logfile-group.html">ALTER LOGFILE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-procedure.html">ALTER PROCEDURE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-server.html">ALTER SERVER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="alter-table.html">ALTER TABLE Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-table-partition-operations.html">ALTER TABLE Partition Operations</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-table-generated-columns.html">ALTER TABLE and Generated Columns</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-table-examples.html">ALTER TABLE Examples</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-tablespace.html">ALTER TABLESPACE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-view.html">ALTER VIEW Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-database.html">CREATE DATABASE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-event.html">CREATE EVENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-function.html">CREATE FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-index.html">CREATE INDEX Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-logfile-group.html">CREATE LOGFILE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-procedure.html">CREATE PROCEDURE and CREATE FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-server.html">CREATE SERVER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-spatial-reference-system.html">CREATE SPATIAL REFERENCE SYSTEM Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-down"></span></a></div><div class="docs-sidebar-nav-link current"><a href="create-table.html">CREATE TABLE Syntax</a></div></div>                        <div class="docs-submenu">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-statement-retention.html">CREATE TABLE Statement Retention</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-files.html">Files Created by CREATE TABLE</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-temporary-table.html">CREATE TEMPORARY TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-like.html">CREATE TABLE ... LIKE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-select.html">CREATE TABLE ... SELECT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-foreign-keys.html">Using FOREIGN KEY Constraints</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-check-constraints.html">CHECK Constraints</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="silent-column-changes.html">Silent Column Specification Changes</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-generated-columns.html">CREATE TABLE and Generated Columns</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-secondary-indexes.html">Secondary Indexes and Generated Columns</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-table-ndb-table-comment-options.html">Setting NDB_TABLE Options</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-tablespace.html">CREATE TABLESPACE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-trigger.html">CREATE TRIGGER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-view.html">CREATE VIEW Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-database.html">DROP DATABASE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-event.html">DROP EVENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-function.html">DROP FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-index.html">DROP INDEX Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-logfile-group.html">DROP LOGFILE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-procedure.html">DROP PROCEDURE and DROP FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-server.html">DROP SERVER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-spatial-reference-system.html">DROP SPATIAL REFERENCE SYSTEM Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-table.html">DROP TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-tablespace.html">DROP TABLESPACE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-trigger.html">DROP TRIGGER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-view.html">DROP VIEW Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="rename-table.html">RENAME TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="truncate-table.html">TRUNCATE TABLE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-data-manipulation.html">Data Manipulation Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="call.html">CALL Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="delete.html">DELETE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="do.html">DO Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="handler.html">HANDLER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="import-table.html">IMPORT TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="insert.html">INSERT Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="insert-select.html">INSERT ... SELECT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="insert-on-duplicate.html">INSERT ... ON DUPLICATE KEY UPDATE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="insert-delayed.html">INSERT DELAYED Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="load-data.html">LOAD DATA Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="load-xml.html">LOAD XML Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="replace.html">REPLACE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="select.html">SELECT Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="select-into.html">SELECT ... INTO Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="join.html">JOIN Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="union.html">UNION Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="subqueries.html">Subquery Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="scalar-subqueries.html">The Subquery as Scalar Operand</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="comparisons-using-subqueries.html">Comparisons Using Subqueries</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="any-in-some-subqueries.html">Subqueries with ANY, IN, or SOME</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="all-subqueries.html">Subqueries with ALL</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="row-subqueries.html">Row Subqueries</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="exists-and-not-exists-subqueries.html">Subqueries with EXISTS or NOT EXISTS</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="correlated-subqueries.html">Correlated Subqueries</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="derived-tables.html">Derived Tables</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="lateral-derived-tables.html">Lateral Derived Tables</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="subquery-errors.html">Subquery Errors</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="optimizing-subqueries.html">Optimizing Subqueries</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="rewriting-subqueries.html">Rewriting Subqueries as Joins</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="update.html">UPDATE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="with.html">WITH Syntax (Common Table Expressions)</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-transactions.html">Transactional and Locking Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="commit.html">START TRANSACTION, COMMIT, and ROLLBACK Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="cannot-roll-back.html">Statements That Cannot Be Rolled Back</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="implicit-commit.html">Statements That Cause an Implicit Commit</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="savepoint.html">SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="lock-instance-for-backup.html">LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="lock-tables.html">LOCK TABLES and UNLOCK TABLES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-transaction.html">SET TRANSACTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="xa.html">XA Transactions</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="xa-statements.html">XA Transaction SQL Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="xa-states.html">XA Transaction States</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-replication.html">Replication Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="replication-master-sql.html">SQL Statements for Controlling Master Servers</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="purge-binary-logs.html">PURGE BINARY LOGS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="reset-master.html">RESET MASTER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-sql-log-bin.html">SET sql_log_bin Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="replication-slave-sql.html">SQL Statements for Controlling Slave Servers</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="change-master-to.html">CHANGE MASTER TO Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="change-replication-filter.html">CHANGE REPLICATION FILTER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="master-pos-wait.html">MASTER_POS_WAIT() Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="reset-slave.html">RESET SLAVE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-global-sql-slave-skip-counter.html">SET GLOBAL sql_slave_skip_counter Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="start-slave.html">START SLAVE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="stop-slave.html">STOP SLAVE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="replication-group-sql.html">SQL Statements for Controlling Group Replication</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="start-group-replication.html">START GROUP_REPLICATION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="stop-group-replication.html">STOP GROUP_REPLICATION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="group-replication-functions-for-new-primary.html">Function which Configures Group Replication Primary</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="group-replication-functions-for-mode.html">Functions which Configure the Group Replication Mode</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="group-replication-functions-for-maximum-consensus.html">Functions to Inspect and Configure the Maximum Consensus Instances of a
        Group</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="group-replication-functions-for-communication-protocol.html">Functions to Inspect and Set the Group Replication Communication
Protocol Version</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-prepared-statements.html">Prepared SQL Statement Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="prepare.html">PREPARE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="execute.html">EXECUTE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="deallocate-prepare.html">DEALLOCATE PREPARE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-compound-statements.html">Compound-Statement Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="begin-end.html">BEGIN ... END Compound-Statement Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="statement-labels.html">Statement Label Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="declare.html">DECLARE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="stored-program-variables.html">Variables in Stored Programs</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="declare-local-variable.html">Local Variable DECLARE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="local-variable-scope.html">Local Variable Scope and Resolution</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="flow-control-statements.html">Flow Control Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="case.html">CASE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="if.html">IF Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="iterate.html">ITERATE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="leave.html">LEAVE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="loop.html">LOOP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="repeat.html">REPEAT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="return.html">RETURN Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="while.html">WHILE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="cursors.html">Cursors</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="close.html">Cursor CLOSE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="declare-cursor.html">Cursor DECLARE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="fetch.html">Cursor FETCH Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="open.html">Cursor OPEN Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="condition-handling.html">Condition Handling</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="declare-condition.html">DECLARE ... CONDITION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="declare-handler.html">DECLARE ... HANDLER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="get-diagnostics.html">GET DIAGNOSTICS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="resignal.html">RESIGNAL Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="signal.html">SIGNAL Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="handler-scope.html">Scope Rules for Handlers</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="diagnostics-area.html">The MySQL Diagnostics Area</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="conditions-and-parameters.html">Condition Handling and OUT or INOUT Parameters</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-server-administration.html">Database Administration Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="account-management-sql.html">Account Management Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-user.html">ALTER USER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-role.html">CREATE ROLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-user.html">CREATE USER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-role.html">DROP ROLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-user.html">DROP USER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="grant.html">GRANT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="rename-user.html">RENAME USER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="revoke.html">REVOKE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-default-role.html">SET DEFAULT ROLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-password.html">SET PASSWORD Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-role.html">SET ROLE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="resource-group-sql.html">Resource Group Management Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="alter-resource-group.html">ALTER RESOURCE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-resource-group.html">CREATE RESOURCE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-resource-group.html">DROP RESOURCE GROUP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-resource-group.html">SET RESOURCE GROUP Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="table-maintenance-sql.html">Table Maintenance Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="analyze-table.html">ANALYZE TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="check-table.html">CHECK TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="checksum-table.html">CHECKSUM TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="optimize-table.html">OPTIMIZE TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="repair-table.html">REPAIR TABLE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="component-sql.html">Component, Plugin, and User-Defined Function Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="create-function-udf.html">CREATE FUNCTION Syntax for User-Defined Functions</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="drop-function-udf.html">DROP FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="install-component.html">INSTALL COMPONENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="install-plugin.html">INSTALL PLUGIN Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="uninstall-component.html">UNINSTALL COMPONENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="uninstall-plugin.html">UNINSTALL PLUGIN Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="set-statement.html">SET Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-variable.html">SET Syntax for Variable Assignment</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-character-set.html">SET CHARACTER SET Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="set-names.html">SET NAMES Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="show.html">SHOW Syntax</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-binary-logs.html">SHOW BINARY LOGS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-binlog-events.html">SHOW BINLOG EVENTS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-character-set.html">SHOW CHARACTER SET Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-collation.html">SHOW COLLATION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-columns.html">SHOW COLUMNS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-database.html">SHOW CREATE DATABASE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-event.html">SHOW CREATE EVENT Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-function.html">SHOW CREATE FUNCTION Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-procedure.html">SHOW CREATE PROCEDURE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-table.html">SHOW CREATE TABLE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-trigger.html">SHOW CREATE TRIGGER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-user.html">SHOW CREATE USER Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-create-view.html">SHOW CREATE VIEW Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-databases.html">SHOW DATABASES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-engine.html">SHOW ENGINE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-engines.html">SHOW ENGINES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-errors.html">SHOW ERRORS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-events.html">SHOW EVENTS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-function-code.html">SHOW FUNCTION CODE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-function-status.html">SHOW FUNCTION STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-grants.html">SHOW GRANTS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-index.html">SHOW INDEX Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-master-status.html">SHOW MASTER STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-open-tables.html">SHOW OPEN TABLES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-plugins.html">SHOW PLUGINS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-privileges.html">SHOW PRIVILEGES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-procedure-code.html">SHOW PROCEDURE CODE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-procedure-status.html">SHOW PROCEDURE STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-processlist.html">SHOW PROCESSLIST Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-profile.html">SHOW PROFILE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-profiles.html">SHOW PROFILES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-relaylog-events.html">SHOW RELAYLOG EVENTS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-slave-hosts.html">SHOW SLAVE HOSTS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-slave-status.html">SHOW SLAVE STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-status.html">SHOW STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-table-status.html">SHOW TABLE STATUS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-tables.html">SHOW TABLES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-triggers.html">SHOW TRIGGERS Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-variables.html">SHOW VARIABLES Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="show-warnings.html">SHOW WARNINGS Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="other-administrative-sql.html">Other Administrative Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="binlog.html">BINLOG Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="cache-index.html">CACHE INDEX Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="flush.html">FLUSH Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="kill.html">KILL Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="load-index.html">LOAD INDEX INTO CACHE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="reset.html">RESET Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="reset-persist.html">RESET PERSIST Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="restart.html">RESTART Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="shutdown.html">SHUTDOWN Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax-utility.html">Utility Statements</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="describe.html">DESCRIBE Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="explain.html">EXPLAIN Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="help.html">HELP Syntax</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="use.html">USE Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="data-dictionary.html">MySQL Data Dictionary</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="innodb-storage-engine.html">The InnoDB Storage Engine</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="storage-engines.html">Alternative Storage Engines</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="replication.html">Replication</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="group-replication.html">Group Replication</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="mysql-shell-userguide.html">MySQL Shell</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="document-store.html">Using MySQL as a Document Store</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-innodb-cluster-userguide.html">InnoDB Cluster</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-cluster.html">MySQL NDB Cluster 8.0</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="partitioning.html">Partitioning</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="stored-objects.html">Stored Objects</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="information-schema.html">INFORMATION_SCHEMA Tables</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="performance-schema.html">MySQL Performance Schema</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sys-schema.html">MySQL sys Schema</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="connectors-apis.html">Connectors and APIs</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="extending-mysql.html">Extending MySQL</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-enterprise.html">MySQL Enterprise Edition</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="workbench.html">MySQL Workbench</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="faqs.html">MySQL 8.0 Frequently Asked Questions</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="error-handling.html">Errors, Error Codes, and Common Problems</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="restrictions.html">Restrictions and Limits</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="indexes.html">Indexes</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="glossary.html">MySQL Glossary</a></div></div>                    </li>
        </ul>
                            </nav>
        </div>

        
<a class="docs-sidebar-section" href=""><span class="icon-related"></span>
    <span class="text">Related Documentation</span></a>
<div class="docs-sidebar-accordian open">
    <div class="text">
        <a href="/doc/relnotes/mysql/8.0/en/">MySQL 8.0 Release Notes</a><br />
        <a href="/doc/dev/mysql-server/latest/">MySQL 8.0 Source Code Documentation</a><br />
        </div>
</div>

    <a class="docs-sidebar-section" href=""><span class="icon-download-thin"></span>
        <span class="text">
            Download
                            this Manual
                    </span>
    </a>
    <div class="docs-sidebar-accordian open">
        <div class="text">
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.pdf">PDF (US Ltr)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.a4.pdf">PDF (A4)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-pdf-1-20190611.noarch.rpm">PDF (RPM)</a>
            - 41.5Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.tar.gz">HTML Download (TGZ)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.zip">HTML Download (Zip)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-html-chapter-1-20190611.noarch.rpm">HTML Download (RPM)</a>
            - 9.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.tar.gz">Man Pages (TGZ)</a>
            - 220.4Kb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.zip">Man Pages (Zip)</a>
            - 325.8Kb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.gz">Info (Gzip)</a>
            - 4.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.zip">Info (Zip)</a>
            - 4.1Mb<br />
                    </div>
    </div>

<a class="docs-sidebar-section" href=""><span class="icon-book"></span>
    <span class="text">Excerpts from this Manual</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
                <a href="/doc/mysql-backup-excerpt/8.0/en/">MySQL Backup and Recovery</a><br />
                <a href="/doc/mysql-g11n-excerpt/8.0/en/">MySQL Globalization</a><br />
                <a href="/doc/mysql-infoschema-excerpt/8.0/en/">MySQL Information Schema</a><br />
                <a href="/doc/mysql-installation-excerpt/8.0/en/">MySQL Installation Guide</a><br />
                <a href="/doc/mysql-security-excerpt/8.0/en/">Security in MySQL</a><br />
                <a href="/doc/mysql-startstop-excerpt/8.0/en/">Starting and Stopping MySQL</a><br />
                <a href="/doc/mysql-linuxunix-excerpt/8.0/en/">MySQL and Linux/Unix</a><br />
                <a href="/doc/mysql-windows-excerpt/8.0/en/">MySQL and Windows</a><br />
                <a href="/doc/mysql-osx-excerpt/8.0/en/">MySQL and OS X</a><br />
                <a href="/doc/mysql-solaris-excerpt/8.0/en/">MySQL and Solaris</a><br />
                <a href="/doc/mysql-sourcebuild-excerpt/8.0/en/">Building MySQL from Source</a><br />
                <a href="/doc/mysql-reslimits-excerpt/8.0/en/">MySQL Restrictions and Limitations</a><br />
                <a href="/doc/mysql-partitioning-excerpt/8.0/en/">MySQL Partitioning</a><br />
                <a href="/doc/mysql-secure-deployment-guide/8.0/en/">MySQL Secure Deployment Guide</a><br />
                <a href="/doc/mysql-tutorial-excerpt/8.0/en/">MySQL Tutorial</a><br />
                <a href="/doc/mysql-perfschema-excerpt/8.0/en/">MySQL Performance Schema</a><br />
                <a href="/doc/mysql-replication-excerpt/8.0/en/">MySQL Replication</a><br />
                <a href="/doc/mysql-repo-excerpt/8.0/en/">Using the MySQL Yum Repository</a><br />
            </div>
</div>

        <br /><span id="wkr"><br /></span>
    </div>
    </div>

    <div id="docs-main" class="has-toc">
    <div id="docs-main-inner">

                <div class="right" id="docs-version-nav">
            <a href="" id="docs-version-nav-toggle">version 8.0
            <span class="icon-chevron-down"></span></a>
            <div id="docs-version-list">
                                                                                            <a                         href="/doc/refman/5.7/en/create-table.html">
                        5.7
                                            </a><br />
                                                                            <a                         href="/doc/refman/5.6/en/create-table.html">
                        5.6
                                            </a><br />
                                                                            <a                         href="/doc/refman/5.5/en/create-table.html">
                        5.5
                                            </a><br />
                                                                        <div id="docs-version-nav-lang">
                                                                                                                                                    <a                                     href="/doc/refman/5.6/ja/create-table.html">
                                    5.6&nbsp;
                                                                            Japanese
                                                                    </a><br />
                                                                                            </div>
                            </div>
        </div>
        
        <div id="docs-show-nav" class="left hidden" style="margin-right: 15px;">
            <a class="docs-show-hide-nav" href="" aria-label="Show Sidebar"
                title="Show Sidebar"><span class="icon-arrow-small-right"></span></a>
        </div>

                        <div id="docs-breadcrumbs">
            <a href="/doc/refman/8.0/en/">MySQL 8.0 Reference Manual</a> &nbsp;/&nbsp;
                            <span id="breadcrumbs-link"><a href="" id="show-breadcrumbs">...</a> &nbsp;/&nbsp;</span>
                <span class="hidden" id="hidden-breadcrumbs">
                                                        <a href="sql-syntax.html">SQL Statement Syntax</a> &nbsp;/&nbsp;
                                                            <a href="sql-syntax-data-definition.html">Data Definition Statements</a> &nbsp;/&nbsp;
                                                                                        </span>
                                        CREATE TABLE Syntax
                                    </div>
        
        <div id="docs-body">
        
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="create-table"></a>13.1.20 CREATE TABLE Syntax</h3>

</div>

</div>

</div>
<div class="toc">
<p><small>[<a href="#" class="tocdetail" onclick="toggle('tocdetail-0'); return false;" onkeypress="if (event.keyCode == 13) { toggle('tocdetail-0'); return false;}">+/-</a>]</small></p>
<dl id="tocdetail-0"><dt><span class="section"><a href="create-table-statement-retention.html">13.1.20.1 CREATE TABLE Statement Retention</a></span></dt><dt><span class="section"><a href="create-table-files.html">13.1.20.2 Files Created by CREATE TABLE</a></span></dt><dt><span class="section"><a href="create-temporary-table.html">13.1.20.3 CREATE TEMPORARY TABLE Syntax</a></span></dt><dt><span class="section"><a href="create-table-like.html">13.1.20.4 CREATE TABLE ... LIKE Syntax</a></span></dt><dt><span class="section"><a href="create-table-select.html">13.1.20.5 CREATE TABLE ... SELECT Syntax</a></span></dt><dt><span class="section"><a href="create-table-foreign-keys.html">13.1.20.6 Using FOREIGN KEY Constraints</a></span></dt><dt><span class="section"><a href="create-table-check-constraints.html">13.1.20.7 CHECK Constraints</a></span></dt><dt><span class="section"><a href="silent-column-changes.html">13.1.20.8 Silent Column Specification Changes</a></span></dt><dt><span class="section"><a href="create-table-generated-columns.html">13.1.20.9 CREATE TABLE and Generated Columns</a></span></dt><dt><span class="section"><a href="create-table-secondary-indexes.html">13.1.20.10 Secondary Indexes and Generated Columns</a></span></dt><dt><span class="section"><a href="create-table-ndb-table-comment-options.html">13.1.20.11 Setting NDB_TABLE Options</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139663176519408"></a><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em class="replaceable">tbl_name</em>
    (<em class="replaceable">create_definition</em>,...)
    [<em class="replaceable">table_options</em>]
    [<em class="replaceable">partition_options</em>]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em class="replaceable">tbl_name</em>
    [(<em class="replaceable">create_definition</em>,...)]
    [<em class="replaceable">table_options</em>]
    [<em class="replaceable">partition_options</em>]
    [IGNORE | REPLACE]
    [AS] <em class="replaceable">query_expression</em>

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em class="replaceable">tbl_name</em>
    { LIKE <em class="replaceable">old_tbl_name</em> | (LIKE <em class="replaceable">old_tbl_name</em>) }

<em class="replaceable">create_definition</em>:
    <em class="replaceable">col_name</em> <em class="replaceable">column_definition</em>
  | {INDEX|KEY} [<em class="replaceable">index_name</em>] [<em class="replaceable">index_type</em>] (<em class="replaceable">key_part</em>,...)
      [<em class="replaceable">index_option</em>] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [<em class="replaceable">index_name</em>] (<em class="replaceable">key_part</em>,...)
      [<em class="replaceable">index_option</em>] ...
  | [CONSTRAINT [<em class="replaceable">symbol</em>]] PRIMARY KEY
      [<em class="replaceable">index_type</em>] (<em class="replaceable">key_part</em>,...)
      [<em class="replaceable">index_option</em>] ...
  | [CONSTRAINT [<em class="replaceable">symbol</em>]] UNIQUE [INDEX|KEY]
      [<em class="replaceable">index_name</em>] [<em class="replaceable">index_type</em>] (<em class="replaceable">key_part</em>,...)
      [<em class="replaceable">index_option</em>] ...
  | [CONSTRAINT [<em class="replaceable">symbol</em>]] FOREIGN KEY
      [<em class="replaceable">index_name</em>] (<em class="replaceable">col_name</em>,...)
      <em class="replaceable">reference_definition</em>
  | <em class="replaceable">check_constraint_definition</em>

<em class="replaceable">column_definition</em>:
    <em class="replaceable">data_type</em> [NOT NULL | NULL] [DEFAULT {<em class="replaceable">literal</em> | (<em class="replaceable">expr</em>)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT '<em class="replaceable">string</em>']
      [COLLATE <em class="replaceable">collation_name</em>]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY}]
      [<em class="replaceable">reference_definition</em>]
      [<em class="replaceable">check_constraint_definition</em>]
  | <em class="replaceable">data_type</em>
      [COLLATE <em class="replaceable">collation_name</em>]
      [GENERATED ALWAYS] AS (<em class="replaceable">expr</em>)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT '<em class="replaceable">string</em>']
      [<em class="replaceable">reference_definition</em>]
      [<em class="replaceable">check_constraint_definition</em>]

<em class="replaceable">data_type</em>:
    (see Chapter 11, Data Types)

<em class="replaceable">key_part</em>: {<em class="replaceable">col_name</em> [(<em class="replaceable">length</em>)] | (<em class="replaceable">expr</em>)} [ASC | DESC]

<em class="replaceable">index_type</em>:
    USING {BTREE | HASH}

<em class="replaceable">index_option</em>:
    KEY_BLOCK_SIZE [=] <em class="replaceable">value</em>
  | <em class="replaceable">index_type</em>
  | WITH PARSER <em class="replaceable">parser_name</em>
  | COMMENT '<em class="replaceable">string</em>'
  | {VISIBLE | INVISIBLE}

<em class="replaceable">check_constraint_definition</em>:
    [CONSTRAINT [<em class="replaceable">symbol</em>]] CHECK (<em class="replaceable">expr</em>) [[NOT] ENFORCED]

<em class="replaceable">reference_definition</em>:
    REFERENCES <em class="replaceable">tbl_name</em> (<em class="replaceable">key_part</em>,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE <em class="replaceable">reference_option</em>]
      [ON UPDATE <em class="replaceable">reference_option</em>]

<em class="replaceable">reference_option</em>:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

<em class="replaceable">table_options</em>:
    <em class="replaceable">table_option</em> [[,] <em class="replaceable">table_option</em>] ...

<em class="replaceable">table_option</em>:
    AUTO_INCREMENT [=] <em class="replaceable">value</em>
  | AVG_ROW_LENGTH [=] <em class="replaceable">value</em>
  | [DEFAULT] CHARACTER SET [=] <em class="replaceable">charset_name</em>
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] <em class="replaceable">collation_name</em>
  | COMMENT [=] '<em class="replaceable">string</em>'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] '<em class="replaceable">connect_string</em>'
  | {DATA|INDEX} DIRECTORY [=] '<em class="replaceable">absolute path to directory</em>'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] <em class="replaceable">engine_name</em>
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] <em class="replaceable">value</em>
  | MAX_ROWS [=] <em class="replaceable">value</em>
  | MIN_ROWS [=] <em class="replaceable">value</em>
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] '<em class="replaceable">string</em>'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] <em class="replaceable">value</em>
  | TABLESPACE <em class="replaceable">tablespace_name</em> [STORAGE {DISK|MEMORY}]
  | UNION [=] (<em class="replaceable">tbl_name</em>[,<em class="replaceable">tbl_name</em>]...)

<em class="replaceable">partition_options</em>:
    PARTITION BY
        { [LINEAR] HASH(<em class="replaceable">expr</em>)
        | [LINEAR] KEY [ALGORITHM={1|2}] (<em class="replaceable">column_list</em>)
        | RANGE{(<em class="replaceable">expr</em>) | COLUMNS(<em class="replaceable">column_list</em>)}
        | LIST{(<em class="replaceable">expr</em>) | COLUMNS(<em class="replaceable">column_list</em>)} }
    [PARTITIONS <em class="replaceable">num</em>]
    [SUBPARTITION BY
        { [LINEAR] HASH(<em class="replaceable">expr</em>)
        | [LINEAR] KEY [ALGORITHM={1|2}] (<em class="replaceable">column_list</em>) }
      [SUBPARTITIONS <em class="replaceable">num</em>]
    ]
    [(<em class="replaceable">partition_definition</em> [, <em class="replaceable">partition_definition</em>] ...)]

<em class="replaceable">partition_definition</em>:
    PARTITION <em class="replaceable">partition_name</em>
        [VALUES
            {LESS THAN {(<em class="replaceable">expr</em> | <em class="replaceable">value_list</em>) | MAXVALUE}
            |
            IN (<em class="replaceable">value_list</em>)}]
        [[STORAGE] ENGINE [=] <em class="replaceable">engine_name</em>]
        [COMMENT [=] '<em class="replaceable">string</em>' ]
        [DATA DIRECTORY [=] '<em class="replaceable">data_dir</em>']
        [INDEX DIRECTORY [=] '<em class="replaceable">index_dir</em>']
        [MAX_ROWS [=] <em class="replaceable">max_number_of_rows</em>]
        [MIN_ROWS [=] <em class="replaceable">min_number_of_rows</em>]
        [TABLESPACE [=] tablespace_name]
        [(<em class="replaceable">subpartition_definition</em> [, <em class="replaceable">subpartition_definition</em>] ...)]

<em class="replaceable">subpartition_definition</em>:
    SUBPARTITION <em class="replaceable">logical_name</em>
        [[STORAGE] ENGINE [=] <em class="replaceable">engine_name</em>]
        [COMMENT [=] '<em class="replaceable">string</em>' ]
        [DATA DIRECTORY [=] '<em class="replaceable">data_dir</em>']
        [INDEX DIRECTORY [=] '<em class="replaceable">index_dir</em>']
        [MAX_ROWS [=] <em class="replaceable">max_number_of_rows</em>]
        [MIN_ROWS [=] <em class="replaceable">min_number_of_rows</em>]
        [TABLESPACE [=] tablespace_name]

<em class="replaceable">query_expression:</em>
    SELECT ...   (<em class="replaceable">Some valid select or union statement</em>)</code></pre><p>
      <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> creates a table with
      the given name. You must have the
      <a class="link" href="privileges-provided.html#priv_create"><code class="literal">CREATE</code></a> privilege for the table.
    </p><p>
      By default, tables are created in the default database, using the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> storage engine. An error
      occurs if the table exists, if there is no default database, or if
      the database does not exist.
    </p><p>
      For information about the physical representation of a table, see
      <a class="xref" href="create-table-files.html" title="13.1.20.2 Files Created by CREATE TABLE">Section 13.1.20.2, “Files Created by CREATE TABLE”</a>.
    </p><p>
      The original <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
      statement, including all specifications and table options are
      stored by MySQL when the table is created. For more information,
      see <a class="xref" href="create-table-statement-retention.html" title="13.1.20.1 CREATE TABLE Statement Retention">Section 13.1.20.1, “CREATE TABLE Statement Retention”</a>.
    </p><p>
      There are several aspects to the <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
      TABLE</code></a> statement, described under the following topics in
      this section:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="create-table.html#create-table-name" title="Table Name">Table Name</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-temporary-tables" title="Temporary Tables">Temporary Tables</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-clone-copy" title="Table Cloning and Copying">Table Cloning and Copying</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-types-attributes" title="Column Data Types and Attributes">Column Data Types and Attributes</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-indexes-keys" title="Indexes, Foreign Keys, and CHECK Constraints">Indexes, Foreign Keys, and CHECK Constraints</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-options" title="Table Options">Table Options</a></p></li><li class="listitem"><p><a class="xref" href="create-table.html#create-table-partitioning" title="Table Partitioning">Table Partitioning</a></p></li></ul>
</div>
<h4><a name="create-table-name"></a>Table Name</h4>

<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>tbl_name</code></em></code>
        </p><p>
          The table name can be specified as
          <em class="replaceable"><code>db_name.tbl_name</code></em> to create the
          table in a specific database. This works regardless of whether
          there is a default database, assuming that the database
          exists. If you use quoted identifiers, quote the database and
          table names separately. For example, write
          <code class="literal">`mydb`.`mytbl`</code>, not
          <code class="literal">`mydb.mytbl`</code>.
        </p><p>
          Rules for permissible table names are given in
          <a class="xref" href="identifiers.html" title="9.2 Schema Object Names">Section 9.2, “Schema Object Names”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">IF NOT EXISTS</code>
        </p><p>
          Prevents an error from occurring if the table exists. However,
          there is no verification that the existing table has a
          structure identical to that indicated by the
          <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement.
</p></li></ul>
</div>
<h4><a name="create-table-temporary-tables"></a>Temporary Tables</h4>
<p>
      You can use the <code class="literal">TEMPORARY</code> keyword when creating
      a table. A <code class="literal">TEMPORARY</code> table is visible only
      within the current session, and is dropped automatically when the
      session is closed. For more information, see
      <a class="xref" href="create-temporary-table.html" title="13.1.20.3 CREATE TEMPORARY TABLE Syntax">Section 13.1.20.3, “CREATE TEMPORARY TABLE Syntax”</a>.
</p>
<h4><a name="create-table-clone-copy"></a>Table Cloning and Copying</h4>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">LIKE</code>
        </p><p>
          Use <code class="literal">CREATE TABLE ... LIKE</code> to create an
          empty table based on the definition of another table,
          including any column attributes and indexes defined in the
          original table:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE <em class="replaceable">new_tbl</em> LIKE <em class="replaceable">orig_tbl</em>;</code></pre><p>
          For more information, see <a class="xref" href="create-table-like.html" title="13.1.20.4 CREATE TABLE ... LIKE Syntax">Section 13.1.20.4, “CREATE TABLE ... LIKE Syntax”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">[AS]
          <em class="replaceable"><code>query_expression</code></em></code>
        </p><p>
          To create one table from another, add a
          <a class="link" href="select.html" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement at the end of
          the <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE <em class="replaceable">new_tbl</em> AS SELECT * FROM <em class="replaceable">orig_tbl</em>;</code></pre><p>
          For more information, see
          <a class="xref" href="create-table-select.html" title="13.1.20.5 CREATE TABLE ... SELECT Syntax">Section 13.1.20.5, “CREATE TABLE ... SELECT Syntax”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">IGNORE|REPLACE</code>
        </p><p>
          The <code class="literal">IGNORE</code> and <code class="literal">REPLACE</code>
          options indicate how to handle rows that duplicate unique key
          values when copying a table using a
          <a class="link" href="select.html" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement.
        </p><p>
          For more information, see
          <a class="xref" href="create-table-select.html" title="13.1.20.5 CREATE TABLE ... SELECT Syntax">Section 13.1.20.5, “CREATE TABLE ... SELECT Syntax”</a>.
</p></li></ul>
</div>
<h4><a name="create-table-types-attributes"></a>Column Data Types and Attributes</h4>
<p>
      There is a hard limit of 4096 columns per table, but the effective
      maximum may be less for a given table and depends on the factors
      discussed in <a class="xref" href="column-count-limit.html" title="C.10.4 Limits on Table Column Count and Row Size">Section C.10.4, “Limits on Table Column Count and Row Size”</a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>data_type</code></em></code>
        </p><p>
          <em class="replaceable"><code>data_type</code></em> represents the data type
          in a column definition. For a full description of the syntax
          available for specifying column data types, as well as
          information about the properties of each type, see
          <a class="xref" href="data-types.html" title="Chapter 11 Data Types">Chapter 11, <i>Data Types</i></a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              Some attributes do not apply to all data types.
              <code class="literal">AUTO_INCREMENT</code> applies only to integer
              and floating-point types. Prior to MySQL 8.0.13,
              <code class="literal">DEFAULT</code> does not apply to the
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>,
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a>,
              <code class="literal">GEOMETRY</code>, and
              <a class="link" href="json.html" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> types.
            </p></li><li class="listitem"><p>
              Character data types (<a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
              <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>, the
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> types,
              <a class="link" href="enum.html" title="11.4.4 The ENUM Type"><code class="literal">ENUM</code></a>,
              <a class="link" href="set.html" title="11.4.5 The SET Type"><code class="literal">SET</code></a>, and any synonyms)
              synonyms) can include <code class="literal">CHARACTER SET</code> to
              specify the character set for the column.
              <code class="literal">CHARSET</code> is a synonym for
              <code class="literal">CHARACTER SET</code>. A collation for the
              character set can be specified with the
              <code class="literal">COLLATE</code> attribute, along with any other
              attributes. For details, see <a class="xref" href="charset.html" title="Chapter 10 Character Sets, Collations, Unicode">Chapter 10, <i>Character Sets, Collations, Unicode</i></a>.
              Example:
            </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);</code></pre><p>
              MySQL 8.0 interprets length specifications in
              character column definitions in characters. Lengths for
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a> and
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a> are in bytes.
            </p></li><li class="listitem"><p>
              For <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
              <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a>, and
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a> columns, indexes
              can be created that use only the leading part of column
              values, using
              <code class="literal"><em class="replaceable"><code>col_name</code></em>(<em class="replaceable"><code>length</code></em>)</code>
              syntax to specify an index prefix length.
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> and
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns also can be
              indexed, but a prefix length <span class="emphasis"><em>must</em></span> be
              given. Prefix lengths are given in characters for
              nonbinary string types and in bytes for binary string
              types. That is, index entries consist of the first
              <em class="replaceable"><code>length</code></em> characters of each
              column value for <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
              <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>, and
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns, and the first
              <em class="replaceable"><code>length</code></em> bytes of each column
              value for <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a>,
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a>, and
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> columns. Indexing only
              a prefix of column values like this can make the index
              file much smaller. For additional information about index
              prefixes, see <a class="xref" href="create-index.html" title="13.1.15 CREATE INDEX Syntax">Section 13.1.15, “CREATE INDEX Syntax”</a>.
            </p><a class="indexterm" name="idm139663176353456"></a><a class="indexterm" name="idm139663176351968"></a><a class="indexterm" name="idm139663176350480"></a><a class="indexterm" name="idm139663176348992"></a><p>
              Only the <code class="literal">InnoDB</code> and
              <code class="literal">MyISAM</code> storage engines support indexing
              on <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> and
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns. For example:
            </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));</code></pre><p>
              If a specified index prefix exceeds the maximum column
              data type size, <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
              TABLE</code></a> handles the index as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: square; "><li class="listitem"><p>
                  For a nonunique index, either an error occurs (if
                  strict SQL mode is enabled), or the index length is
                  reduced to lie within the maximum column data type
                  size and a warning is produced (if strict SQL mode is
                  not enabled).
                </p></li><li class="listitem"><p>
                  For a unique index, an error occurs regardless of SQL
                  mode because reducing the index length might enable
                  insertion of nonunique entries that do not meet the
                  specified uniqueness requirement.
</p></li></ul>
</div>
</li><li class="listitem"><p>
              <a class="link" href="json.html" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> columns cannot be
              indexed. You can work around this restriction by creating
              an index on a generated column that extracts a scalar
              value from the <code class="literal">JSON</code> column. See
              <a class="xref" href="create-table-secondary-indexes.html#json-column-indirect-index" title="Indexing a Generated Column to Provide a JSON Column Index">Indexing a Generated Column to Provide a JSON Column Index</a>, for a
              detailed example.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          <code class="literal">NOT NULL | NULL</code>
        </p><a class="indexterm" name="idm139663176332384"></a><a class="indexterm" name="idm139663176330896"></a><p>
          If neither <code class="literal">NULL</code> nor <code class="literal">NOT
          NULL</code> is specified, the column is treated as though
          <code class="literal">NULL</code> had been specified.
        </p><p>
          In MySQL 8.0, only the <code class="literal">InnoDB</code>,
          <code class="literal">MyISAM</code>, and <code class="literal">MEMORY</code>
          storage engines support indexes on columns that can have
          <code class="literal">NULL</code> values. In other cases, you must
          declare indexed columns as <code class="literal">NOT NULL</code> or an
          error results.
        </p></li><li class="listitem"><p>
          <code class="literal">DEFAULT</code>
        </p><a class="indexterm" name="idm139663176321184"></a><a class="indexterm" name="idm139663176320112"></a><p>
          Specifies a default value for a column. For more information
          about default value handling, including the case that a column
          definition includes no explicit <code class="literal">DEFAULT</code>
          value, see <a class="xref" href="data-type-defaults.html" title="11.7 Data Type Default Values">Section 11.7, “Data Type Default Values”</a>.
        </p><p>
          If the <a class="link" href="sql-mode.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a> or
          <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date"><code class="literal">NO_ZERO_IN_DATE</code></a> SQL mode is
          enabled and a date-valued default is not correct according to
          that mode, <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
          produces a warning if strict SQL mode is not enabled and an
          error if strict mode is enabled. For example, with
          <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date"><code class="literal">NO_ZERO_IN_DATE</code></a> enabled,
          <code class="literal">c1 DATE DEFAULT '2010-00-00'</code> produces a
          warning.
        </p></li><li class="listitem"><p>
          <code class="literal">AUTO_INCREMENT</code>
        </p><p>
          An integer or floating-point column can have the additional
          attribute <code class="literal">AUTO_INCREMENT</code>. When you insert a
          value of <code class="literal">NULL</code> (recommended) or
          <code class="literal">0</code> into an indexed
          <code class="literal">AUTO_INCREMENT</code> column, the column is set to
          the next sequence value. Typically this is
          <code class="literal"><em class="replaceable"><code>value</code></em>+1</code>, where
          <em class="replaceable"><code>value</code></em> is the largest value for the
          column currently in the table.
          <code class="literal">AUTO_INCREMENT</code> sequences begin with
          <code class="literal">1</code>.
        </p><p>
          To retrieve an <code class="literal">AUTO_INCREMENT</code> value after
          inserting a row, use the
          <a class="link" href="information-functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> SQL function
          or the <a class="link" href="mysql-insert-id.html" title="28.7.7.38 mysql_insert_id()"><code class="literal">mysql_insert_id()</code></a> C API
          function. See <a class="xref" href="information-functions.html" title="12.15 Information Functions">Section 12.15, “Information Functions”</a>, and
          <a class="xref" href="mysql-insert-id.html" title="28.7.7.38 mysql_insert_id()">Section 28.7.7.38, “mysql_insert_id()”</a>.
        </p><p>
          If the <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero"><code class="literal">NO_AUTO_VALUE_ON_ZERO</code></a>
          SQL mode is enabled, you can store <code class="literal">0</code> in
          <code class="literal">AUTO_INCREMENT</code> columns as
          <code class="literal">0</code> without generating a new sequence value.
          See <a class="xref" href="sql-mode.html" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>.
        </p><p>
          There can be only one <code class="literal">AUTO_INCREMENT</code> column
          per table, it must be indexed, and it cannot have a
          <code class="literal">DEFAULT</code> value. An
          <code class="literal">AUTO_INCREMENT</code> column works properly only
          if it contains only positive values. Inserting a negative
          number is regarded as inserting a very large positive number.
          This is done to avoid precision problems when numbers
          <span class="quote">“<span class="quote">wrap</span>”</span> over from positive to negative and also to
          ensure that you do not accidentally get an
          <code class="literal">AUTO_INCREMENT</code> column that contains
          <code class="literal">0</code>.
        </p><p>
          For <code class="literal">MyISAM</code> tables, you can specify an
          <code class="literal">AUTO_INCREMENT</code> secondary column in a
          multiple-column key. See
          <a class="xref" href="example-auto-increment.html" title="3.6.9 Using AUTO_INCREMENT">Section 3.6.9, “Using AUTO_INCREMENT”</a>.
        </p><a class="indexterm" name="idm139663176285760"></a><a class="indexterm" name="idm139663176284688"></a><p>
          To make MySQL compatible with some ODBC applications, you can
          find the <code class="literal">AUTO_INCREMENT</code> value for the last
          inserted row with the following query:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">SELECT * FROM <em class="replaceable">tbl_name</em> WHERE <em class="replaceable">auto_col</em> IS NULL</code></pre><p>
          This method requires that
          <a class="link" href="server-system-variables.html#sysvar_sql_auto_is_null"><code class="literal">sql_auto_is_null</code></a> variable is
          not set to 0. See <a class="xref" href="server-system-variables.html" title="5.1.8 Server System Variables">Section 5.1.8, “Server System Variables”</a>.
        </p><p>
          For information about <code class="literal">InnoDB</code> and
          <code class="literal">AUTO_INCREMENT</code>, see
          <a class="xref" href="innodb-auto-increment-handling.html" title="15.6.1.4 AUTO_INCREMENT Handling in InnoDB">Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”</a>. For
          information about <code class="literal">AUTO_INCREMENT</code> and MySQL
          Replication, see
          <a class="xref" href="replication-features-auto-increment.html" title="17.4.1.1 Replication and AUTO_INCREMENT">Section 17.4.1.1, “Replication and AUTO_INCREMENT”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">COMMENT</code>
        </p><a class="indexterm" name="idm139663176272160"></a><p>
          A comment for a column can be specified with the
          <code class="literal">COMMENT</code> option, up to 1024 characters long.
          The comment is displayed by the <a class="link" href="show-create-table.html" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW
          CREATE TABLE</code></a> and
          <a class="link" href="show-columns.html" title="13.7.6.5 SHOW COLUMNS Syntax"><code class="literal">SHOW FULL
          COLUMNS</code></a> statements.
        </p></li><li class="listitem"><p>
          <code class="literal">COLUMN_FORMAT</code>
        </p><a class="indexterm" name="idm139663176265936"></a><p>
          In NDB Cluster, it is also possible to specify a data storage
          format for individual columns of
          <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables using
          <code class="literal">COLUMN_FORMAT</code>. Permissible column formats
          are <code class="literal">FIXED</code>, <code class="literal">DYNAMIC</code>, and
          <code class="literal">DEFAULT</code>. <code class="literal">FIXED</code> is used
          to specify fixed-width storage, <code class="literal">DYNAMIC</code>
          permits the column to be variable-width, and
          <code class="literal">DEFAULT</code> causes the column to use
          fixed-width or variable-width storage as determined by the
          column's data type (possibly overridden by a
          <code class="literal">ROW_FORMAT</code> specifier).
        </p><p>
          For <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables, the default value
          for <code class="literal">COLUMN_FORMAT</code> is
          <code class="literal">FIXED</code>.
        </p><p>
          In NDB Cluster, the maximum possible offset for a column
          defined with <code class="literal">COLUMN_FORMAT=FIXED</code> is 8188
          bytes. For more information and possible workarounds, see
          <a class="xref" href="mysql-cluster-limitations-database-objects.html" title="22.1.7.5 Limits Associated with Database Objects in NDB Cluster">Section 22.1.7.5, “Limits Associated with Database Objects in NDB Cluster”</a>.
        </p><p>
          <code class="literal">COLUMN_FORMAT</code> currently has no effect on
          columns of tables using storage engines other than
          <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a>. MySQL 8.0
          silently ignores <code class="literal">COLUMN_FORMAT</code>.
        </p></li><li class="listitem"><p>
          <code class="literal">STORAGE</code>
        </p><a class="indexterm" name="idm139663176247488"></a><p>
          For <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables, it is possible to
          specify whether the column is stored on disk or in memory by
          using a <code class="literal">STORAGE</code> clause. <code class="literal">STORAGE
          DISK</code> causes the column to be stored on disk, and
          <code class="literal">STORAGE MEMORY</code> causes in-memory storage to
          be used. The <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
          statement used must still include a
          <code class="literal">TABLESPACE</code> clause:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; CREATE TABLE t1 (
    -&gt;     c1 INT STORAGE DISK,
    -&gt;     c2 INT STORAGE MEMORY
    -&gt; ) ENGINE NDB;
ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)

mysql&gt; CREATE TABLE t1 (
    -&gt;     c1 INT STORAGE DISK,
    -&gt;     c2 INT STORAGE MEMORY
    -&gt; ) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.06 sec)</code></pre><p>
          For <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables, <code class="literal">STORAGE
          DEFAULT</code> is equivalent to <code class="literal">STORAGE
          MEMORY</code>.
        </p><p>
          The <code class="literal">STORAGE</code> clause has no effect on tables
          using storage engines other than
          <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a>. The
          <code class="literal">STORAGE</code> keyword is supported only in the
          build of <a class="link" href="mysqld.html" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> that is supplied with NDB
          Cluster; it is not recognized in any other version of MySQL,
          where any attempt to use the <code class="literal">STORAGE</code>
          keyword causes a syntax error.
        </p></li><li class="listitem"><p>
          <code class="literal">GENERATED ALWAYS</code>
        </p><p>
          Used to specify a generated column expression. For information
          about <a class="link" href="glossary.html#glos_generated_column" title="generated column">generated
          columns</a>, see
          <a class="xref" href="create-table-generated-columns.html" title="13.1.20.9 CREATE TABLE and Generated Columns">Section 13.1.20.9, “CREATE TABLE and Generated Columns”</a>.
        </p><p>
          <a class="link" href="glossary.html#glos_stored_generated_column" title="stored generated column">Stored generated
          columns</a> can be indexed. <code class="literal">InnoDB</code>
          supports secondary indexes on
          <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">virtual
          generated columns</a>. See
          <a class="xref" href="create-table-secondary-indexes.html" title="13.1.20.10 Secondary Indexes and Generated Columns">Section 13.1.20.10, “Secondary Indexes and Generated Columns”</a>.
</p></li></ul>
</div>
<h4><a name="create-table-indexes-keys"></a>Indexes, Foreign Keys, and CHECK Constraints</h4>
<p>
      Several keywords apply to creation of indexes, foreign keys, and
      <code class="literal">CHECK</code> constraints. For general background in
      addition to the following descriptions, see
      <a class="xref" href="create-index.html" title="13.1.15 CREATE INDEX Syntax">Section 13.1.15, “CREATE INDEX Syntax”</a>,
      <a class="xref" href="create-table-foreign-keys.html" title="13.1.20.6 Using FOREIGN KEY Constraints">Section 13.1.20.6, “Using FOREIGN KEY Constraints”</a>, and
      <a class="xref" href="create-table-check-constraints.html" title="13.1.20.7 CHECK Constraints">Section 13.1.20.7, “CHECK Constraints”</a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">CONSTRAINT
          <em class="replaceable"><code>symbol</code></em></code>
        </p><p>
          The <code class="literal">CONSTRAINT
          <em class="replaceable"><code>symbol</code></em></code> clause may be
          given to name a constraint. If the clause is not given, or a
          <em class="replaceable"><code>symbol</code></em> is not included following
          the <code class="literal">CONSTRAINT</code> keyword, MySQL automatically
          generates a constraint name, with the exception noted below.
          The <em class="replaceable"><code>symbol</code></em> value, if used, must be
          unique per schema (database), per constraint type. A duplicate
          <em class="replaceable"><code>symbol</code></em> results in an error.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            If the <code class="literal">CONSTRAINT
            <em class="replaceable"><code>symbol</code></em></code> clause is not
            given in a foreign key definition, or a
            <em class="replaceable"><code>symbol</code></em> is not included following
            the <code class="literal">CONSTRAINT</code> keyword, MySQL uses the
            foreign key index name up to MySQL 8.0.15, and automatically
            generates a constraint name thereafter.
</p>
</div>
<p>
          The SQL standard specifies that all types of constraints
          (primary key, unique index, foreign key, check) belong to the
          same namespace. In MySQL, each constraint type has its own
          namespace per schema. Consequently, names for each type of
          constraint must be unique per schema.
        </p></li><li class="listitem"><p>
          <code class="literal">PRIMARY KEY</code>
        </p><a class="indexterm" name="idm139663176201568"></a><p>
          A unique index where all key columns must be defined as
          <code class="literal">NOT NULL</code>. If they are not explicitly
          declared as <code class="literal">NOT NULL</code>, MySQL declares them
          so implicitly (and silently). A table can have only one
          <code class="literal">PRIMARY KEY</code>. The name of a <code class="literal">PRIMARY
          KEY</code> is always <code class="literal">PRIMARY</code>, which thus
          cannot be used as the name for any other kind of index.
        </p><p>
          If you do not have a <code class="literal">PRIMARY KEY</code> and an
          application asks for the <code class="literal">PRIMARY KEY</code> in
          your tables, MySQL returns the first <code class="literal">UNIQUE</code>
          index that has no <code class="literal">NULL</code> columns as the
          <code class="literal">PRIMARY KEY</code>.
        </p><p>
          In <code class="literal">InnoDB</code> tables, keep the <code class="literal">PRIMARY
          KEY</code> short to minimize storage overhead for secondary
          indexes. Each secondary index entry contains a copy of the
          primary key columns for the corresponding row. (See
          <a class="xref" href="innodb-index-types.html" title="15.6.2.1 Clustered and Secondary Indexes">Section 15.6.2.1, “Clustered and Secondary Indexes”</a>.)
        </p><p>
          In the created table, a <code class="literal">PRIMARY KEY</code> is
          placed first, followed by all <code class="literal">UNIQUE</code>
          indexes, and then the nonunique indexes. This helps the MySQL
          optimizer to prioritize which index to use and also more
          quickly to detect duplicated <code class="literal">UNIQUE</code> keys.
        </p><p>
          A <code class="literal">PRIMARY KEY</code> can be a multiple-column
          index. However, you cannot create a multiple-column index
          using the <code class="literal">PRIMARY KEY</code> key attribute in a
          column specification. Doing so only marks that single column
          as primary. You must use a separate <code class="literal">PRIMARY
          KEY(<em class="replaceable"><code>key_part</code></em>, ...)</code>
          clause.
        </p><a class="indexterm" name="idm139663176183360"></a><p>
          If a table has a <code class="literal">PRIMARY KEY</code> or
          <code class="literal">UNIQUE NOT NULL</code> index that consists of a
          single column that has an integer type, you can use
          <code class="literal">_rowid</code> to refer to the indexed column in
          <a class="link" href="select.html" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statements, as described
          in <a class="xref" href="create-index.html#create-index-unique" title="Unique Indexes">Unique Indexes</a>.
        </p><p>
          In MySQL, the name of a <code class="literal">PRIMARY KEY</code> is
          <code class="literal">PRIMARY</code>. For other indexes, if you do not
          assign a name, the index is assigned the same name as the
          first indexed column, with an optional suffix
          (<code class="literal">_2</code>, <code class="literal">_3</code>,
          <code class="literal">...</code>) to make it unique. You can see index
          names for a table using <code class="literal">SHOW INDEX FROM
          <em class="replaceable"><code>tbl_name</code></em></code>. See
          <a class="xref" href="show-index.html" title="13.7.6.22 SHOW INDEX Syntax">Section 13.7.6.22, “SHOW INDEX Syntax”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">KEY | INDEX</code>
        </p><p>
          <code class="literal">KEY</code> is normally a synonym for
          <code class="literal">INDEX</code>. The key attribute <code class="literal">PRIMARY
          KEY</code> can also be specified as just
          <code class="literal">KEY</code> when given in a column definition. This
          was implemented for compatibility with other database systems.
        </p></li><li class="listitem"><p>
          <code class="literal">UNIQUE</code>
        </p><p>
          A <code class="literal">UNIQUE</code> index creates a constraint such
          that all values in the index must be distinct. An error occurs
          if you try to add a new row with a key value that matches an
          existing row. For all engines, a <code class="literal">UNIQUE</code>
          index permits multiple <code class="literal">NULL</code> values for
          columns that can contain <code class="literal">NULL</code>. If you
          specify a prefix value for a column in a
          <code class="literal">UNIQUE</code> index, the column values must be
          unique within the prefix length.
        </p><a class="indexterm" name="idm139663176160672"></a><p>
          If a table has a <code class="literal">PRIMARY KEY</code> or
          <code class="literal">UNIQUE NOT NULL</code> index that consists of a
          single column that has an integer type, you can use
          <code class="literal">_rowid</code> to refer to the indexed column in
          <a class="link" href="select.html" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statements, as described
          in <a class="xref" href="create-index.html#create-index-unique" title="Unique Indexes">Unique Indexes</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">FULLTEXT</code>
        </p><p>
          A <code class="literal">FULLTEXT</code> index is a special type of index
          used for full-text searches. Only the
          <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> and
          <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> storage engines support
          <code class="literal">FULLTEXT</code> indexes. They can be created only
          from <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
          <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>, and
          <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns. Indexing always
          happens over the entire column; column prefix indexing is not
          supported and any prefix length is ignored if specified. See
          <a class="xref" href="fulltext-search.html" title="12.9 Full-Text Search Functions">Section 12.9, “Full-Text Search Functions”</a>, for details of operation. A
          <code class="literal">WITH PARSER</code> clause can be specified as an
          <em class="replaceable"><code>index_option</code></em> value to associate a
          parser plugin with the index if full-text indexing and
          searching operations need special handling. This clause is
          valid only for <code class="literal">FULLTEXT</code> indexes.
          <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> and
          <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> support full-text parser
          plugins. See <a class="xref" href="plugin-types.html#full-text-plugin-type" title="Full-Text Parser Plugins">Full-Text Parser Plugins</a> and
          <a class="xref" href="writing-full-text-plugins.html" title="29.2.4.4 Writing Full-Text Parser Plugins">Section 29.2.4.4, “Writing Full-Text Parser Plugins”</a> for more
          information.
        </p></li><li class="listitem"><p>
          <code class="literal">SPATIAL</code>
        </p><p>
          You can create <code class="literal">SPATIAL</code> indexes on spatial
          data types. Spatial types are supported only for
          <code class="literal">InnoDB</code> and <code class="literal">MyISAM</code>
          tables, and indexed columns must be declared as <code class="literal">NOT
          NULL</code>. See <a class="xref" href="spatial-types.html" title="11.5 Spatial Data Types">Section 11.5, “Spatial Data Types”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">FOREIGN KEY</code>
        </p><p>
          MySQL supports foreign keys, which let you cross-reference
          related data across tables, and foreign key constraints, which
          help keep this spread-out data consistent. For definition and
          option information, see
          <a class="link" href="create-table.html#create-table-reference-definition"><em class="replaceable"><code>reference_definition</code></em></a>,
          and
          <a class="link" href="create-table.html#create-table-reference-option"><em class="replaceable"><code>reference_option</code></em></a>.
        </p><p>
          Partitioned tables employing the
          <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> storage engine do not
          support foreign keys. See
          <a class="xref" href="partitioning-limitations.html" title="23.6 Restrictions and Limitations on Partitioning">Section 23.6, “Restrictions and Limitations on Partitioning”</a>, for more
          information.
        </p></li><li class="listitem"><p>
          <code class="literal">CHECK</code>
        </p><p>
          The <code class="literal">CHECK</code> clause enables the creation of
          constraints to be checked for data values in table rows. See
          <a class="xref" href="create-table-check-constraints.html" title="13.1.20.7 CHECK Constraints">Section 13.1.20.7, “CHECK Constraints”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>key_part</code></em></code>
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              A <em class="replaceable"><code>key_part</code></em> specification can
              end with <code class="literal">ASC</code> or <code class="literal">DESC</code>
              to specify whether index values are stored in ascending or
              descending order. The default is ascending if no order
              specifier is given.
            </p></li><li class="listitem"><p>
              Prefixes, defined by the <em class="replaceable"><code>length</code></em>
              attribute, can be up to 767 bytes long for
              <code class="literal">InnoDB</code> tables that use the
              <code class="literal"><a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">REDUNDANT</a></code>
              or
              <code class="literal"><a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">COMPACT</a></code>
              row format. The prefix length limit is 3072 bytes for
              <code class="literal">InnoDB</code> tables that use the
              <code class="literal"><a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">DYNAMIC</a></code>
              or
              <code class="literal"><a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">COMPRESSED</a></code>
              row format. For <code class="literal">MyISAM</code> tables, the
              prefix length limit is 1000 bytes.
            </p><p>
              Prefix <span class="emphasis"><em>limits</em></span> are measured in bytes.
              However, prefix <span class="emphasis"><em>lengths</em></span> for index
              specifications in <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
              TABLE</code></a>, <a class="link" href="alter-table.html" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER
              TABLE</code></a>, and <a class="link" href="create-index.html" title="13.1.15 CREATE INDEX Syntax"><code class="literal">CREATE
              INDEX</code></a> statements are interpreted as number of
              characters for nonbinary string types
              (<a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
              <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a>) and number of bytes
              for binary string types
              (<a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a>,
              <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a>,
              <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>). Take this into
              account when specifying a prefix length for a nonbinary
              string column that uses a multibyte character set.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>index_type</code></em></code>
        </p><p>
          Some storage engines permit you to specify an index type when
          creating an index. The syntax for the
          <em class="replaceable"><code>index_type</code></em> specifier is
          <code class="literal">USING <em class="replaceable"><code>type_name</code></em></code>.
        </p><p>
          Example:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;</code></pre><p>
          The preferred position for <code class="literal">USING</code> is after
          the index column list. It can be given before the column list,
          but support for use of the option in that position is
          deprecated and will be removed in a future MySQL release.
        </p></li><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>index_option</code></em></code>
        </p><p>
          <em class="replaceable"><code>index_option</code></em> values specify
          additional options for an index.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              <code class="literal">KEY_BLOCK_SIZE</code>
            </p><p>
              For <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> tables,
              <code class="literal">KEY_BLOCK_SIZE</code> optionally specifies the
              size in bytes to use for index key blocks. The value is
              treated as a hint; a different size could be used if
              necessary. A <code class="literal">KEY_BLOCK_SIZE</code> value
              specified for an individual index definition overrides the
              table-level <code class="literal">KEY_BLOCK_SIZE</code> value.
            </p><p>
              For information about the table-level
              <code class="literal">KEY_BLOCK_SIZE</code> attribute, see
              <a class="xref" href="create-table.html#create-table-options" title="Table Options">Table Options</a>.
            </p></li><li class="listitem"><p>
              <code class="literal">WITH PARSER</code>
            </p><p>
              The <code class="literal">WITH PARSER</code> option can only be used
              with <code class="literal">FULLTEXT</code> indexes. It associates a
              parser plugin with the index if full-text indexing and
              searching operations need special handling.
              <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> and
              <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> support full-text
              parser plugins. If you have a
              <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> table with an
              associated full-text parser plugin, you can convert the
              table to <code class="literal">InnoDB</code> using <code class="literal">ALTER
              TABLE</code>.
            </p></li><li class="listitem"><p>
              <code class="literal">COMMENT</code>
            </p><p>
              In MySQL 8.0, index definitions can include
              an optional comment of up to 1024 characters.
            </p><p>
              You can set the <code class="literal">InnoDB</code>
              <code class="literal">MERGE_THRESHOLD</code> value for an individual
              index using the
              <code class="literal"><em class="replaceable"><code>index_option</code></em></code>
              <code class="literal">COMMENT</code> clause. See
              <a class="xref" href="index-page-merge-threshold.html" title="15.8.11 Configuring the Merge Threshold for Index Pages">Section 15.8.11, “Configuring the Merge Threshold for Index Pages”</a>.
</p></li></ul>
</div>
<p>
          For more information about permissible
          <em class="replaceable"><code>index_option</code></em> values, see
          <a class="xref" href="create-index.html" title="13.1.15 CREATE INDEX Syntax">Section 13.1.15, “CREATE INDEX Syntax”</a>. For more information about
          indexes, see <a class="xref" href="mysql-indexes.html" title="8.3.1 How MySQL Uses Indexes">Section 8.3.1, “How MySQL Uses Indexes”</a>.
        </p></li><li class="listitem"><p><a name="create-table-reference-definition"></a>
          <code class="literal"><em class="replaceable"><code>reference_definition</code></em></code>
        </p><p>
          For <em class="replaceable"><code>reference_definition</code></em> syntax
          details and examples, see
          <a class="xref" href="create-table-foreign-keys.html" title="13.1.20.6 Using FOREIGN KEY Constraints">Section 13.1.20.6, “Using FOREIGN KEY Constraints”</a>. For information
          specific to foreign keys in <code class="literal">InnoDB</code>, see
          <a class="xref" href="innodb-foreign-key-constraints.html" title="15.6.1.5 InnoDB and FOREIGN KEY Constraints">Section 15.6.1.5, “InnoDB and FOREIGN KEY Constraints”</a>.
        </p><p>
          <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> and
          <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables support checking of
          foreign key constraints. The columns of the referenced table
          must always be explicitly named. Both <code class="literal">ON
          DELETE</code> and <code class="literal">ON UPDATE</code> actions on
          foreign keys are supported. For more detailed information and
          examples, see <a class="xref" href="create-table-foreign-keys.html" title="13.1.20.6 Using FOREIGN KEY Constraints">Section 13.1.20.6, “Using FOREIGN KEY Constraints”</a>. For
          information specific to foreign keys in
          <code class="literal">InnoDB</code>, see
          <a class="xref" href="innodb-foreign-key-constraints.html" title="15.6.1.5 InnoDB and FOREIGN KEY Constraints">Section 15.6.1.5, “InnoDB and FOREIGN KEY Constraints”</a>.
        </p><p>
          For other storage engines, MySQL Server parses and ignores the
          <code class="literal">FOREIGN KEY</code> and
          <code class="literal">REFERENCES</code> syntax in
          <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements. See
          <a class="xref" href="ansi-diff-foreign-keys.html" title="1.8.2.3 Foreign Key Differences">Section 1.8.2.3, “Foreign Key Differences”</a>.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
            For users familiar with the ANSI/ISO SQL Standard, please
            note that no storage engine, including
            <code class="literal">InnoDB</code>, recognizes or enforces the
            <code class="literal">MATCH</code> clause used in referential
            integrity constraint definitions. Use of an explicit
            <code class="literal">MATCH</code> clause will not have the specified
            effect, and also causes <code class="literal">ON DELETE</code> and
            <code class="literal">ON UPDATE</code> clauses to be ignored. For
            these reasons, specifying <code class="literal">MATCH</code> should be
            avoided.
          </p><p>
            The <code class="literal">MATCH</code> clause in the SQL standard
            controls how <code class="literal">NULL</code> values in a composite
            (multiple-column) foreign key are handled when comparing to
            a primary key. <code class="literal">InnoDB</code> essentially
            implements the semantics defined by <code class="literal">MATCH
            SIMPLE</code>, which permit a foreign key to be all or
            partially <code class="literal">NULL</code>. In that case, the (child
            table) row containing such a foreign key is permitted to be
            inserted, and does not match any row in the referenced
            (parent) table. It is possible to implement other semantics
            using triggers.
          </p><p>
            Additionally, MySQL requires that the referenced columns be
            indexed for performance. However, <code class="literal">InnoDB</code>
            does not enforce any requirement that the referenced columns
            be declared <code class="literal">UNIQUE</code> or <code class="literal">NOT
            NULL</code>. The handling of foreign key references to
            nonunique keys or keys that contain <code class="literal">NULL</code>
            values is not well defined for operations such as
            <code class="literal">UPDATE</code> or <code class="literal">DELETE
            CASCADE</code>. You are advised to use foreign keys that
            reference only keys that are both <code class="literal">UNIQUE</code>
            (or <code class="literal">PRIMARY</code>) and <code class="literal">NOT
            NULL</code>.
          </p><p>
            MySQL parses but ignores <span class="quote">“<span class="quote">inline
            <code class="literal">REFERENCES</code> specifications</span>”</span> (as
            defined in the SQL standard) where the references are
            defined as part of the column specification. MySQL accepts
            <code class="literal">REFERENCES</code> clauses only when specified as
            part of a separate <code class="literal">FOREIGN KEY</code>
            specification.
</p>
</div>
</li><li class="listitem"><p><a name="create-table-reference-option"></a>
          <code class="literal"><em class="replaceable"><code>reference_option</code></em></code>
        </p><p>
          For information about the <code class="literal">RESTRICT</code>,
          <code class="literal">CASCADE</code>, <code class="literal">SET NULL</code>,
          <code class="literal">NO ACTION</code>, and <code class="literal">SET
          DEFAULT</code> options, see
          <a class="xref" href="create-table-foreign-keys.html" title="13.1.20.6 Using FOREIGN KEY Constraints">Section 13.1.20.6, “Using FOREIGN KEY Constraints”</a>.
</p></li></ul>
</div>
<h4><a name="create-table-options"></a>Table Options</h4>
<p>
      Table options are used to optimize the behavior of the table. In
      most cases, you do not have to specify any of them. These options
      apply to all storage engines unless otherwise indicated. Options
      that do not apply to a given storage engine may be accepted and
      remembered as part of the table definition. Such options then
      apply if you later use <a class="link" href="alter-table.html" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
      to convert the table to use a different storage engine.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">ENGINE</code>
        </p><p>
          Specifies the storage engine for the table, using one of the
          names shown in the following table. The engine name can be
          unquoted or quoted. The quoted name
          <code class="literal">'DEFAULT'</code> is recognized but ignored.
</p>
<div class="informaltable">
<table summary="Storage engine names permitted for the ENGINE table option and a description of each engine."><col width="25%"><col width="70%"><thead><tr>
              <th scope="col">Storage Engine</th>
              <th scope="col">Description</th>
            </tr></thead><tbody><tr>
              <td scope="row"><code class="literal">InnoDB</code></td>
              <td>Transaction-safe tables with row locking and foreign keys. The default
                storage engine for new tables. See
                <a class="xref" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine">Chapter 15, <i>The InnoDB Storage Engine</i></a>, and in
                particular <a class="xref" href="innodb-introduction.html" title="15.1 Introduction to InnoDB">Section 15.1, “Introduction to InnoDB”</a> if you
                have MySQL experience but are new to
                <code class="literal">InnoDB</code>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">MyISAM</code></td>
              <td>The binary portable storage engine that is primarily used for read-only
                or read-mostly workloads. See
                <a class="xref" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine">Section 16.2, “The MyISAM Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">MEMORY</code></td>
              <td>The data for this storage engine is stored only in memory. See
                <a class="xref" href="memory-storage-engine.html" title="16.3 The MEMORY Storage Engine">Section 16.3, “The MEMORY Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">CSV</code></td>
              <td>Tables that store rows in comma-separated values format. See
                <a class="xref" href="csv-storage-engine.html" title="16.4 The CSV Storage Engine">Section 16.4, “The CSV Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">ARCHIVE</code></td>
              <td>The archiving storage engine. See
                <a class="xref" href="archive-storage-engine.html" title="16.5 The ARCHIVE Storage Engine">Section 16.5, “The ARCHIVE Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">EXAMPLE</code></td>
              <td>An example engine. See <a class="xref" href="example-storage-engine.html" title="16.9 The EXAMPLE Storage Engine">Section 16.9, “The EXAMPLE Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">FEDERATED</code></td>
              <td>Storage engine that accesses remote tables. See
                <a class="xref" href="federated-storage-engine.html" title="16.8 The FEDERATED Storage Engine">Section 16.8, “The FEDERATED Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">HEAP</code></td>
              <td>This is a synonym for <code class="literal">MEMORY</code>.</td>
            </tr><tr>
              <td scope="row"><code class="literal">MERGE</code></td>
              <td>A collection of <code class="literal">MyISAM</code> tables used as one table. Also
                known as <code class="literal">MRG_MyISAM</code>. See
                <a class="xref" href="merge-storage-engine.html" title="16.7 The MERGE Storage Engine">Section 16.7, “The MERGE Storage Engine”</a>.</td>
            </tr><tr>
              <td scope="row"><a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a></td>
              <td>Clustered, fault-tolerant, memory-based tables, supporting transactions
                and foreign keys. Also known as
                <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDBCLUSTER</code></a>. See
                <a class="xref" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0">Chapter 22, <i>MySQL NDB Cluster 8.0</i></a>.</td>
</tr></tbody></table>
</div>
<p>
          By default, if a storage engine is specified that is not
          available, the statement fails with an error. You can override
          this behavior by removing
          <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution"><code class="literal">NO_ENGINE_SUBSTITUTION</code></a> from
          the server SQL mode (see <a class="xref" href="sql-mode.html" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>) so that
          MySQL allows substitution of the specified engine with the
          default storage engine instead. Normally in such cases, this
          is <code class="literal">InnoDB</code>, which is the default value for
          the <a class="link" href="server-system-variables.html#sysvar_default_storage_engine"><code class="literal">default_storage_engine</code></a>
          system variable. When
          <code class="literal">NO_ENGINE_SUBSTITUTION</code> is disabled, a
          warning occurs if the storage engine specification is not
          honored.
        </p></li><li class="listitem"><p>
          <code class="literal">AUTO_INCREMENT</code>
        </p><p>
          The initial <code class="literal">AUTO_INCREMENT</code> value for the
          table. In MySQL 8.0, this works for
          <code class="literal">MyISAM</code>, <code class="literal">MEMORY</code>,
          <code class="literal">InnoDB</code>, and <code class="literal">ARCHIVE</code>
          tables. To set the first auto-increment value for engines that
          do not support the <code class="literal">AUTO_INCREMENT</code> table
          option, insert a <span class="quote">“<span class="quote">dummy</span>”</span> row with a value one
          less than the desired value after creating the table, and then
          delete the dummy row.
        </p><p>
          For engines that support the <code class="literal">AUTO_INCREMENT</code>
          table option in <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
          statements, you can also use <code class="literal">ALTER TABLE
          <em class="replaceable"><code>tbl_name</code></em> AUTO_INCREMENT =
          <em class="replaceable"><code>N</code></em></code> to reset the
          <code class="literal">AUTO_INCREMENT</code> value. The value cannot be
          set lower than the maximum value currently in the column.
        </p></li><li class="listitem"><p>
          <code class="literal">AVG_ROW_LENGTH</code>
        </p><p>
          An approximation of the average row length for your table. You
          need to set this only for large tables with variable-size
          rows.
        </p><p>
          When you create a <code class="literal">MyISAM</code> table, MySQL uses
          the product of the <code class="literal">MAX_ROWS</code> and
          <code class="literal">AVG_ROW_LENGTH</code> options to decide how big
          the resulting table is. If you don't specify either option,
          the maximum size for <code class="literal">MyISAM</code> data and index
          files is 256TB by default. (If your operating system does not
          support files that large, table sizes are constrained by the
          file size limit.) If you want to keep down the pointer sizes
          to make the index smaller and faster and you don't really need
          big files, you can decrease the default pointer size by
          setting the
          <a class="link" href="server-system-variables.html#sysvar_myisam_data_pointer_size"><code class="literal">myisam_data_pointer_size</code></a>
          system variable. (See
          <a class="xref" href="server-system-variables.html" title="5.1.8 Server System Variables">Section 5.1.8, “Server System Variables”</a>.) If you want all
          your tables to be able to grow above the default limit and are
          willing to have your tables slightly slower and larger than
          necessary, you can increase the default pointer size by
          setting this variable. Setting the value to 7 permits table
          sizes up to 65,536TB.
        </p></li><li class="listitem"><p>
          <code class="literal">[DEFAULT] CHARACTER SET</code>
        </p><p>
          Specifies a default character set for the table.
          <code class="literal">CHARSET</code> is a synonym for <code class="literal">CHARACTER
          SET</code>. If the character set name is
          <code class="literal">DEFAULT</code>, the database character set is
          used.
        </p></li><li class="listitem"><p>
          <code class="literal">CHECKSUM</code>
        </p><p>
          Set this to 1 if you want MySQL to maintain a live checksum
          for all rows (that is, a checksum that MySQL updates
          automatically as the table changes). This makes the table a
          little slower to update, but also makes it easier to find
          corrupted tables. The <a class="link" href="checksum-table.html" title="13.7.3.3 CHECKSUM TABLE Syntax"><code class="literal">CHECKSUM
          TABLE</code></a> statement reports the checksum.
          (<code class="literal">MyISAM</code> only.)
        </p></li><li class="listitem"><p>
          <code class="literal">[DEFAULT] COLLATE</code>
        </p><p>
          Specifies a default collation for the table.
        </p></li><li class="listitem"><p>
          <code class="literal">COMMENT</code>
        </p><p>
          A comment for the table, up to 2048 characters long.
        </p><p>
          You can set the <code class="literal">InnoDB</code>
          <code class="literal">MERGE_THRESHOLD</code> value for a table using the
          <code class="literal"><em class="replaceable"><code>table_option</code></em></code>
          <code class="literal">COMMENT</code> clause. See
          <a class="xref" href="index-page-merge-threshold.html" title="15.8.11 Configuring the Merge Threshold for Index Pages">Section 15.8.11, “Configuring the Merge Threshold for Index Pages”</a>.
        </p><p><a name="create-table-comment-ndb-table-options"></a><b>Setting NDB_TABLE options. </b>
            <a class="indexterm" name="idm139663175923968"></a>

            The table comment in a <code class="literal">CREATE TABLE</code> that
            creates an <code class="literal">NDB</code> table or an
            <a class="link" href="alter-table.html" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement which
            alters one can also be used to specify one to four of the
            <code class="literal">NDB_TABLE</code> options
            <code class="literal">NOLOGGING</code>,
            <code class="literal">READ_BACKUP</code>,
            <code class="literal">PARTITION_BALANCE</code>, or
            <code class="literal">FULLY_REPLICATED</code> as a set of name-value
            pairs, separated by commas if need be, immediately following
            the string <code class="literal">NDB_TABLE=</code> that begins the
            quoted comment text. An example statement using this syntax
            is shown here (emphasized text):
          </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100) )
ENGINE=NDB
<em>COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE"</em>;</code></pre><p>
          Spaces are not permitted within the quoted string. The string
          is case-insensitive.
        </p><p>
          The comment is displayed as part of the ouput of
          <a class="link" href="show-create-table.html" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>. The text of
          the comment is also available as the TABLE_COMMENT column of
          the MySQL Information Schema
          <a class="link" href="tables-table.html" title="25.30 The INFORMATION_SCHEMA TABLES Table"><code class="literal">TABLES</code></a> table.
        </p><p>
          This comment syntax is also supported with
          <a class="link" href="alter-table.html" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statements for
          <code class="literal">NDB</code> tables. Keep in mind that a table
          comment used with <code class="literal">ALTER TABLE</code> replaces any
          existing comment which the table might have had perviously.
        </p><p>
          Setting the <code class="literal">MERGE_THRESHOLD</code> option in table
          comments is not supported for <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a>
          tables (it is ignored).
        </p><p>
          For complete syntax information and examples, see
          <a class="xref" href="create-table-ndb-table-comment-options.html" title="13.1.20.11 Setting NDB_TABLE Options">Section 13.1.20.11, “Setting NDB_TABLE Options”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">COMPRESSION</code>
        </p><p>
          The compression algorithm used for page level compression for
          <code class="literal">InnoDB</code> tables. Supported values include
          <code class="literal">Zlib</code>, <code class="literal">LZ4</code>, and
          <code class="literal">None</code>. The <code class="literal">COMPRESSION</code>
          attribute was introduced with the transparent page compression
          feature. Page compression is only supported with
          <code class="literal">InnoDB</code> tables that reside in
          <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">file-per-table</a>
          tablespaces, and is only available on Linux and Windows
          platforms that support sparse files and hole punching. For
          more information, see
          <a class="xref" href="innodb-page-compression.html" title="15.9.2 InnoDB Page Compression">Section 15.9.2, “InnoDB Page Compression”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">CONNECTION</code>
        </p><p>
          The connection string for a <code class="literal">FEDERATED</code>
          table.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Older versions of MySQL used a <code class="literal">COMMENT</code>
            option for the connection string.
</p>
</div>
</li><li class="listitem"><p>
          <code class="literal">DATA DIRECTORY</code>, <code class="literal">INDEX
          DIRECTORY</code>
        </p><p>
          For <code class="literal">InnoDB</code>, the <code class="literal">DATA
          DIRECTORY='<em class="replaceable"><code>directory</code></em>'</code>
          clause permits creating a file-per-table tablespace outside of
          the data directory. The tablespace data file is created in the
          specified directory, inside a subdirectory with the same name
          as the schema. The
          <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table"><code class="literal">innodb_file_per_table</code></a>
          variable must be enabled to use the <code class="literal">DATA
          DIRECTORY</code> clause. The full directory path must be
          specified. For more information, see
          <a class="xref" href="tablespace-placing.html" title="15.6.3.6 Creating a Tablespace Outside of the Data Directory">Section 15.6.3.6, “Creating a Tablespace Outside of the Data Directory”</a>.
        </p><p>
          When creating <code class="literal">MyISAM</code> tables, you can use
          the <code class="literal">DATA
          DIRECTORY='<em class="replaceable"><code>directory</code></em>'</code>
          clause, the <code class="literal">INDEX
          DIRECTORY='<em class="replaceable"><code>directory</code></em>'</code>
          clause, or both. They specify where to put a
          <code class="literal">MyISAM</code> table's data file and index file,
          respectively. Unlike <code class="literal">InnoDB</code> tables, MySQL
          does not create subdirectories that correspond to the database
          name when creating a <code class="literal">MyISAM</code> table with a
          <code class="literal">DATA DIRECTORY</code> or <code class="literal">INDEX
          DIRECTORY</code> option. Files are created in the directory
          that is specified.
        </p><p>
          You must have the <a class="link" href="privileges-provided.html#priv_file"><code class="literal">FILE</code></a>
          privilege to use the <code class="literal">DATA DIRECTORY</code> or
          <code class="literal">INDEX DIRECTORY</code> table option.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
            Table-level <code class="literal">DATA DIRECTORY</code> and
            <code class="literal">INDEX DIRECTORY</code> options are ignored for
            partitioned tables. (Bug #32091)
</p>
</div>
<p>
          These options work only when you are not using the
          <a class="link" href="server-options.html#option_mysqld_symbolic-links"><code class="option">--skip-symbolic-links</code></a>
          option. Your operating system must also have a working,
          thread-safe <code class="literal">realpath()</code> call. See
          <a class="xref" href="symbolic-links-to-tables.html" title="8.12.2.2 Using Symbolic Links for MyISAM Tables on Unix">Section 8.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix”</a>, for more complete
          information.
        </p><p>
          If a <code class="literal">MyISAM</code> table is created with no
          <code class="literal">DATA DIRECTORY</code> option, the
          <code class="filename">.MYD</code> file is created in the database
          directory. By default, if <code class="literal">MyISAM</code> finds an
          existing <code class="filename">.MYD</code> file in this case, it
          overwrites it. The same applies to <code class="filename">.MYI</code>
          files for tables created with no <code class="literal">INDEX
          DIRECTORY</code> option. To suppress this behavior, start
          the server with the
          <a class="link" href="server-system-variables.html#sysvar_keep_files_on_create"><code class="option">--keep_files_on_create</code></a> option,
          in which case <code class="literal">MyISAM</code> will not overwrite
          existing files and returns an error instead.
        </p><p>
          If a <code class="literal">MyISAM</code> table is created with a
          <code class="literal">DATA DIRECTORY</code> or <code class="literal">INDEX
          DIRECTORY</code> option and an existing
          <code class="filename">.MYD</code> or <code class="filename">.MYI</code> file is
          found, MyISAM always returns an error. It will not overwrite a
          file in the specified directory.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
            You cannot use path names that contain the MySQL data
            directory with <code class="literal">DATA DIRECTORY</code> or
            <code class="literal">INDEX DIRECTORY</code>. This includes
            partitioned tables and individual table partitions. (See Bug
            #32167.)
</p>
</div>
</li><li class="listitem"><p>
          <code class="literal">DELAY_KEY_WRITE</code>
        </p><p>
          Set this to 1 if you want to delay key updates for the table
          until the table is closed. See the description of the
          <a class="link" href="server-system-variables.html#sysvar_delay_key_write"><code class="literal">delay_key_write</code></a> system
          variable in <a class="xref" href="server-system-variables.html" title="5.1.8 Server System Variables">Section 5.1.8, “Server System Variables”</a>.
          (<code class="literal">MyISAM</code> only.)
        </p></li><li class="listitem"><p>
          <code class="literal">ENCRYPTION</code>
        </p><p>
          The <code class="literal">ENCRYPTION</code> clause enables or disables
          page-level data encryption for an <code class="literal">InnoDB</code>
          table. A keyring plugin must be installed and configured
          before encryption can be enabled. Prior to MySQL 8.0.16, the
          <code class="literal">ENCRYPTION</code> clause can only be specified
          when creating a table in an a file-per-table tablespace. As of
          MySQL 8.0.16, the <code class="literal">ENCRYPTION</code> clause can
          also be specified when creating a table in a general
          tablespace.
        </p><p>
          As of MySQL 8.0.16, a table inherits the default schema
          encryption if an <code class="literal">ENCRYPTION</code> clause is not
          specified. If the
          <a class="link" href="server-system-variables.html#sysvar_table_encryption_privilege_check"><code class="literal">table_encryption_privilege_check</code></a>
          variable is enabled, the
          <a class="link" href="privileges-provided.html#priv_table-encryption-admin"><code class="literal">TABLE_ENCRYPTION_ADMIN</code></a>
          privilege is required to create a table with an
          <code class="literal">ENCRYPTION</code> clause setting that differs from
          the default schema encryption. When creating a table in a
          general tablespace, table and tablespace encryption must
          match.
        </p><p>
          As of MySQL 8.0.16, specifying an
          <code class="literal">ENCRYPTION</code> clause with a value other than
          <code class="literal">'N'</code> or <code class="literal">''</code> is not
          permitted when using a storage engine that does not support
          encryption. Previously, the clause was accepted.
        </p><p>
          For more information, see
          <a class="xref" href="innodb-tablespace-encryption.html" title="15.6.3.9 InnoDB Data-at-Rest Encryption">Section 15.6.3.9, “InnoDB Data-at-Rest Encryption”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">INSERT_METHOD</code>
        </p><p>
          If you want to insert data into a <code class="literal">MERGE</code>
          table, you must specify with <code class="literal">INSERT_METHOD</code>
          the table into which the row should be inserted.
          <code class="literal">INSERT_METHOD</code> is an option useful for
          <code class="literal">MERGE</code> tables only. Use a value of
          <code class="literal">FIRST</code> or <code class="literal">LAST</code> to have
          inserts go to the first or last table, or a value of
          <code class="literal">NO</code> to prevent inserts. See
          <a class="xref" href="merge-storage-engine.html" title="16.7 The MERGE Storage Engine">Section 16.7, “The MERGE Storage Engine”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">KEY_BLOCK_SIZE</code>
        </p><p>
          For <a class="link" href="myisam-storage-engine.html" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a> tables,
          <code class="literal">KEY_BLOCK_SIZE</code> optionally specifies the
          size in bytes to use for index key blocks. The value is
          treated as a hint; a different size could be used if
          necessary. A <code class="literal">KEY_BLOCK_SIZE</code> value specified
          for an individual index definition overrides the table-level
          <code class="literal">KEY_BLOCK_SIZE</code> value.
        </p><p>
          For <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> tables,
          <code class="literal">KEY_BLOCK_SIZE</code> specifies the
          <a class="link" href="glossary.html#glos_page" title="page">page</a> size in kilobytes to use
          for <a class="link" href="glossary.html#glos_compression" title="compression">compressed</a>
          <code class="literal">InnoDB</code> tables. The
          <code class="literal">KEY_BLOCK_SIZE</code> value is treated as a hint;
          a different size could be used by <code class="literal">InnoDB</code> if
          necessary. <code class="literal">KEY_BLOCK_SIZE</code> can only be less
          than or equal to the
          <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size"><code class="literal">innodb_page_size</code></a> value. A
          value of 0 represents the default compressed page size, which
          is half of the
          <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size"><code class="literal">innodb_page_size</code></a> value.
          Depending on
          <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size"><code class="literal">innodb_page_size</code></a>, possible
          <code class="literal">KEY_BLOCK_SIZE</code> values include 0, 1, 2, 4,
          8, and 16. See <a class="xref" href="innodb-table-compression.html" title="15.9.1 InnoDB Table Compression">Section 15.9.1, “InnoDB Table Compression”</a> for
          more information.
        </p><p>
          Oracle recommends enabling
          <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a> when
          specifying <code class="literal">KEY_BLOCK_SIZE</code> for
          <code class="literal">InnoDB</code> tables. When
          <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a> is
          enabled, specifying an invalid
          <code class="literal">KEY_BLOCK_SIZE</code> value returns an error. If
          <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a> is
          disabled, an invalid <code class="literal">KEY_BLOCK_SIZE</code> value
          results in a warning, and the
          <code class="literal">KEY_BLOCK_SIZE</code> option is ignored.
        </p><p>
          The <code class="literal">Create_options</code> column in response to
          <a class="link" href="show-table-status.html" title="13.7.6.36 SHOW TABLE STATUS Syntax"><code class="literal">SHOW TABLE STATUS</code></a> reports the
          actual <code class="literal">KEY_BLOCK_SIZE</code> used by the table, as
          does <a class="link" href="show-create-table.html" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>.
        </p><p>
          <code class="literal">InnoDB</code> only supports
          <code class="literal">KEY_BLOCK_SIZE</code> at the table level.
        </p><p>
          <code class="literal">KEY_BLOCK_SIZE</code> is not supported with 32KB
          and 64KB <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size"><code class="literal">innodb_page_size</code></a>
          values. <code class="literal">InnoDB</code> table compression does not
          support these pages sizes.
        </p><p>
          <code class="literal">InnoDB</code> does not support the
          <code class="literal">KEY_BLOCK_SIZE</code> option when creating
          temporary tables.
        </p></li><li class="listitem"><p>
          <code class="literal">MAX_ROWS</code>
        </p><p>
          The maximum number of rows you plan to store in the table.
          This is not a hard limit, but rather a hint to the storage
          engine that the table must be able to store at least this many
          rows.
</p><a class="indexterm" name="idm139663175787856"></a><a class="indexterm" name="idm139663175786368"></a><a class="indexterm" name="idm139663175784880"></a><a class="indexterm" name="idm139663175783392"></a>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
            The use of <code class="literal">MAX_ROWS</code> with
            <code class="literal">NDB</code> tables to control the number of table
            partitions is deprecated. It remains supported in later
            versions for backward compatibility, but is subject to
            removal in a future release. Use PARTITION_BALANCE instead;
            see
            <a class="xref" href="create-table.html#create-table-comment-ndb-table-options" title="Setting NDB_TABLE options">Setting NDB_TABLE options</a>.
</p>
</div>
<p>
          The <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> storage engine treats
          this value as a maximum. If you plan to create very large NDB
          Cluster tables (containing millions of rows), you should use
          this option to insure that <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a>
          allocates sufficient number of index slots in the hash table
          used for storing hashes of the table's primary keys by
          setting <code class="literal">MAX_ROWS = 2 *
          <em class="replaceable"><code>rows</code></em></code>, where
          <em class="replaceable"><code>rows</code></em> is the number of rows that you
          expect to insert into the table.
        </p><p>
          The maximum <code class="literal">MAX_ROWS</code> value is 4294967295;
          larger values are truncated to this limit.
        </p></li><li class="listitem"><p>
          <code class="literal">MIN_ROWS</code>
        </p><p>
          The minimum number of rows you plan to store in the table. The
          <a class="link" href="memory-storage-engine.html" title="16.3 The MEMORY Storage Engine"><code class="literal">MEMORY</code></a> storage engine uses this
          option as a hint about memory use.
        </p></li><li class="listitem"><p>
          <code class="literal">PACK_KEYS</code>
        </p><p>
          Takes effect only with <code class="literal">MyISAM</code> tables. Set
          this option to 1 if you want to have smaller indexes. This
          usually makes updates slower and reads faster. Setting the
          option to 0 disables all packing of keys. Setting it to
          <code class="literal">DEFAULT</code> tells the storage engine to pack
          only long <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>,
          <a class="link" href="char.html" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
          <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a>, or
          <a class="link" href="binary-varbinary.html" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a> columns.
        </p><p>
          If you do not use <code class="literal">PACK_KEYS</code>, the default is
          to pack strings, but not numbers. If you use
          <code class="literal">PACK_KEYS=1</code>, numbers are packed as well.
        </p><p>
          When packing binary number keys, MySQL uses prefix
          compression:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              Every key needs one extra byte to indicate how many bytes
              of the previous key are the same for the next key.
            </p></li><li class="listitem"><p>
              The pointer to the row is stored in high-byte-first order
              directly after the key, to improve compression.
</p></li></ul>
</div>
<p>
          This means that if you have many equal keys on two consecutive
          rows, all following <span class="quote">“<span class="quote">same</span>”</span> keys usually only take
          two bytes (including the pointer to the row). Compare this to
          the ordinary case where the following keys takes
          <code class="literal">storage_size_for_key + pointer_size</code> (where
          the pointer size is usually 4). Conversely, you get a
          significant benefit from prefix compression only if you have
          many numbers that are the same. If all keys are totally
          different, you use one byte more per key, if the key is not a
          key that can have <code class="literal">NULL</code> values. (In this
          case, the packed key length is stored in the same byte that is
          used to mark if a key is <code class="literal">NULL</code>.)
        </p></li><li class="listitem"><p>
          <code class="literal">PASSWORD</code>
        </p><p>
          This option is unused.
        </p></li><li class="listitem"><p>
          <code class="literal">ROW_FORMAT</code>
        </p><p>
          Defines the physical format in which the rows are stored.
        </p><p>
          When executing a <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
          statement with <a class="link" href="glossary.html#glos_strict_mode" title="strict mode">strict
          mode</a> disabled, if you specify a row format that is not
          supported by the storage engine that is used for the table,
          the table is created using that storage engine's default
          row format. The actual row format of the table is reported in
          the <code class="literal">Row_format</code> and
          <code class="literal">Create_options</code> columns in response to
          <a class="link" href="show-table-status.html" title="13.7.6.36 SHOW TABLE STATUS Syntax"><code class="literal">SHOW TABLE STATUS</code></a>.
          <a class="link" href="show-create-table.html" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a> also reports
          the actual row format of the table.
        </p><p>
          Row format choices differ depending on the storage engine used
          for the table.
        </p><p>
          For <code class="literal">InnoDB</code> tables:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              The default row format is defined by
              <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format"><code class="literal">innodb_default_row_format</code></a>,
              which has a default setting of <code class="literal">DYNAMIC</code>.
              The default row format is used when the
              <code class="literal">ROW_FORMAT</code> option is not defined or
              when <code class="literal">ROW_FORMAT=DEFAULT</code> is used.
            </p><p>
              If the <code class="literal">ROW_FORMAT</code> option is not
              defined, or if <code class="literal">ROW_FORMAT=DEFAULT</code> is
              used, operations that rebuild a table also silently change
              the row format of the table to the default defined by
              <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format"><code class="literal">innodb_default_row_format</code></a>.
              For more information, see
              <a class="xref" href="innodb-row-format.html#innodb-row-format-defining" title="Defining the Row Format of a Table">Defining the Row Format of a Table</a>.
            </p></li><li class="listitem"><p>
              For more efficient <code class="literal">InnoDB</code> storage of
              data types, especially <a class="link" href="blob.html" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>
              types, use the <code class="literal">DYNAMIC</code>. See
              <a class="xref" href="innodb-row-format.html#innodb-row-format-dynamic" title="DYNAMIC Row Format">DYNAMIC Row Format</a> for
              requirements associated with the
              <code class="literal">DYNAMIC</code> row format.
            </p></li><li class="listitem"><p>
              To enable compression for <code class="literal">InnoDB</code>
              tables, specify <code class="literal">ROW_FORMAT=COMPRESSED</code>.
              The <code class="literal">ROW_FORMAT=COMPRESSED</code> option is not
              supported when creating temporary tables. See
              <a class="xref" href="innodb-compression.html" title="15.9 InnoDB Table and Page Compression">Section 15.9, “InnoDB Table and Page Compression”</a> for requirements
              associated with the <code class="literal">COMPRESSED</code> row
              format.
            </p></li><li class="listitem"><p>
              The row format used in older versions of MySQL can still
              be requested by specifying the
              <code class="literal">REDUNDANT</code> row format.
            </p></li><li class="listitem"><p>
              When you specify a non-default
              <code class="literal">ROW_FORMAT</code> clause, consider also
              enabling the
              <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a>
              configuration option.
            </p></li><li class="listitem"><p>
              <code class="literal">ROW_FORMAT=FIXED</code> is not supported. If
              <code class="literal">ROW_FORMAT=FIXED</code> is specified while
              <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a> is
              disabled, <code class="literal">InnoDB</code> issues a warning and
              assumes <code class="literal">ROW_FORMAT=DYNAMIC</code>. If
              <code class="literal">ROW_FORMAT=FIXED</code> is specified while
              <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode"><code class="literal">innodb_strict_mode</code></a> is
              enabled, which is the default, <code class="literal">InnoDB</code>
              returns an error.
            </p></li><li class="listitem"><p>
              For additional information about <code class="literal">InnoDB</code>
              row formats, see <a class="xref" href="innodb-row-format.html" title="15.10 InnoDB Row Formats">Section 15.10, “InnoDB Row Formats”</a>.
</p></li></ul>
</div>
<p>
          For <code class="literal">MyISAM</code> tables, the option value can be
          <code class="literal">FIXED</code> or <code class="literal">DYNAMIC</code> for
          static or variable-length row format.
          <a class="link" href="myisampack.html" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a> sets the type to
          <code class="literal">COMPRESSED</code>. See
          <a class="xref" href="myisam-table-formats.html" title="16.2.3 MyISAM Table Storage Formats">Section 16.2.3, “MyISAM Table Storage Formats”</a>.
        </p><p>
          For <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables, the default
          <code class="literal">ROW_FORMAT</code> is <code class="literal">DYNAMIC</code>.
        </p></li><li class="listitem"><p>
          <code class="literal">STATS_AUTO_RECALC</code>
        </p><p>
          Specifies whether to automatically recalculate
          <a class="ulink" href="https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_persistent_statistics" target="_top">persistent
          statistics</a> for an <code class="literal">InnoDB</code> table. The
          value <code class="literal">DEFAULT</code> causes the persistent
          statistics setting for the table to be determined by the
          <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_auto_recalc"><code class="literal">innodb_stats_auto_recalc</code></a>
          configuration option. The value <code class="literal">1</code> causes
          statistics to be recalculated when 10% of the data in the
          table has changed. The value <code class="literal">0</code> prevents
          automatic recalculation for this table; with this setting,
          issue an <a class="link" href="analyze-table.html" title="13.7.3.1 ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>
          statement to recalculate the statistics after making
          substantial changes to the table. For more information about
          the persistent statistics feature, see
          <a class="xref" href="innodb-persistent-stats.html" title="15.8.10.1 Configuring Persistent Optimizer Statistics Parameters">Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">STATS_PERSISTENT</code>
        </p><p>
          Specifies whether to enable
          <a class="ulink" href="https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_persistent_statistics" target="_top">persistent
          statistics</a> for an <code class="literal">InnoDB</code> table. The
          value <code class="literal">DEFAULT</code> causes the persistent
          statistics setting for the table to be determined by the
          <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent"><code class="literal">innodb_stats_persistent</code></a>
          configuration option. The value <code class="literal">1</code> enables
          persistent statistics for the table, while the value
          <code class="literal">0</code> turns off this feature. After enabling
          persistent statistics through a <code class="literal">CREATE
          TABLE</code> or <code class="literal">ALTER TABLE</code> statement,
          issue an <a class="link" href="analyze-table.html" title="13.7.3.1 ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>
          statement to calculate the statistics, after loading
          representative data into the table. For more information about
          the persistent statistics feature, see
          <a class="xref" href="innodb-persistent-stats.html" title="15.8.10.1 Configuring Persistent Optimizer Statistics Parameters">Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">STATS_SAMPLE_PAGES</code>
        </p><p>
          The number of index pages to sample when estimating
          cardinality and other statistics for an indexed column, such
          as those calculated by <a class="link" href="analyze-table.html" title="13.7.3.1 ANALYZE TABLE Syntax"><code class="literal">ANALYZE
          TABLE</code></a>. For more information, see
          <a class="xref" href="innodb-persistent-stats.html" title="15.8.10.1 Configuring Persistent Optimizer Statistics Parameters">Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">TABLESPACE</code>
        </p><p>
          The <code class="literal">TABLESPACE</code> clause can be used to create
          a table in an existing general tablespace, a file-per-table
          tablespace, or the system tablespace.
        </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE <em class="replaceable">tbl_name</em> ... TABLESPACE [=] <em class="replaceable">tablespace_name</em></code></pre><p>
          The general tablespace that you specify must exist prior to
          using the <code class="literal">TABLESPACE</code> clause. For
          information about general tablespaces, see
          <a class="xref" href="general-tablespaces.html" title="15.6.3.3 General Tablespaces">Section 15.6.3.3, “General Tablespaces”</a>.
        </p><p>
          The
          <code class="literal"><em class="replaceable"><code>tablespace_name</code></em></code>
          is a case-sensitive identifier. It may be quoted or unquoted.
          The forward slash character (<span class="quote">“<span class="quote">/</span>”</span>) is not
          permitted. Names beginning with <span class="quote">“<span class="quote">innodb_</span>”</span> are
          reserved for special use.
        </p><p>
          To create a table in the system tablespace, specify
          <code class="literal">innodb_system</code> as the tablespace name.
        </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE <em class="replaceable">tbl_name</em> ... TABLESPACE [=] innodb_system</code></pre><p>
          Using <code class="literal">TABLESPACE [=] innodb_system</code>, you can
          place a table of any uncompressed row format in the system
          tablespace regardless of the
          <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table"><code class="literal">innodb_file_per_table</code></a>
          setting. For example, you can add a table with
          <code class="literal">ROW_FORMAT=DYNAMIC</code> to the system tablespace
          using <code class="literal">TABLESPACE [=] innodb_system</code>.
        </p><p>
          To create a table in a file-per-table tablespace, specify
          <code class="literal">innodb_file_per_table</code> as the tablespace
          name.
</p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">CREATE TABLE <em class="replaceable">tbl_name</em> ... TABLESPACE [=] innodb_file_per_table</code></pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            If <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table"><code class="literal">innodb_file_per_table</code></a> is
            enabled, you need not specify
            <code class="literal">TABLESPACE=innodb_file_per_table</code> to
            create an <code class="literal">InnoDB</code> file-per-table
            tablespace. <code class="literal">InnoDB</code> tables are created in
            file-per-table tablespaces by default when
            <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table"><code class="literal">innodb_file_per_table</code></a> is
            enabled.
</p>
</div>
<p>
          The <code class="literal">DATA DIRECTORY</code> clause is permitted with
          <code class="literal">CREATE TABLE ...
          TABLESPACE=innodb_file_per_table</code> but is otherwise
          not supported for use in combination with the
          <code class="literal">TABLESPACE</code> clause.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Support for <code class="literal">TABLESPACE =
            innodb_file_per_table</code> and <code class="literal">TABLESPACE =
            innodb_temporary</code> clauses with
            <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
            TEMPORARY TABLE</code></a> is deprecated as of MySQL 8.0.13
            and will be removed in a future version of MySQL.
</p>
</div>
<p>
          The <code class="literal">STORAGE</code> table option is employed only
          with <a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> tables.
          <code class="literal">STORAGE</code> determines the type of storage used
          (disk or memory), and can be either <code class="literal">DISK</code> or
          <code class="literal">MEMORY</code>.
        </p><p>
          <code class="literal">TABLESPACE ... STORAGE DISK</code> assigns a table
          to an NDB Cluster Disk Data tablespace. The tablespace must
          already have been created using <a class="link" href="create-tablespace.html" title="13.1.21 CREATE TABLESPACE Syntax"><code class="literal">CREATE
          TABLESPACE</code></a>. See
          <a class="xref" href="mysql-cluster-disk-data.html" title="22.5.13 NDB Cluster Disk Data Tables">Section 22.5.13, “NDB Cluster Disk Data Tables”</a>, for more
          information.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
            A <code class="literal">STORAGE</code> clause cannot be used in a
            <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement
            without a <code class="literal">TABLESPACE</code> clause.
</p>
</div>
</li><li class="listitem"><p>
          <a class="link" href="union.html" title="13.2.10.3 UNION Syntax"><code class="literal">UNION</code></a>
        </p><p>
          Used to access a collection of identical
          <code class="literal">MyISAM</code> tables as one. This works only with
          <code class="literal">MERGE</code> tables. See
          <a class="xref" href="merge-storage-engine.html" title="16.7 The MERGE Storage Engine">Section 16.7, “The MERGE Storage Engine”</a>.
        </p><p>
          You must have <a class="link" href="privileges-provided.html#priv_select"><code class="literal">SELECT</code></a>,
          <a class="link" href="privileges-provided.html#priv_update"><code class="literal">UPDATE</code></a>, and
          <a class="link" href="privileges-provided.html#priv_delete"><code class="literal">DELETE</code></a> privileges for the
          tables you map to a <code class="literal">MERGE</code> table.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Formerly, all tables used had to be in the same database as
            the <code class="literal">MERGE</code> table itself. This restriction
            no longer applies.
</p>
</div>
</li></ul>
</div>
<h4><a name="create-table-partitioning"></a>Table Partitioning</h4>
<p>
      <em class="replaceable"><code>partition_options</code></em> can be used to
      control partitioning of the table created with
      <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>.
    </p><p>
      Not all options shown in the syntax for
      <em class="replaceable"><code>partition_options</code></em> at the beginning of
      this section are available for all partitioning types. Please see
      the listings for the following individual types for information
      specific to each type, and see <a class="xref" href="partitioning.html" title="Chapter 23 Partitioning">Chapter 23, <i>Partitioning</i></a>, for
      more complete information about the workings of and uses for
      partitioning in MySQL, as well as additional examples of table
      creation and other statements relating to MySQL partitioning.
    </p><p>
      Partitions can be modified, merged, added to tables, and dropped
      from tables. For basic information about the MySQL statements to
      accomplish these tasks, see <a class="xref" href="alter-table.html" title="13.1.9 ALTER TABLE Syntax">Section 13.1.9, “ALTER TABLE Syntax”</a>. For
      more detailed descriptions and examples, see
      <a class="xref" href="partitioning-management.html" title="23.3 Partition Management">Section 23.3, “Partition Management”</a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">PARTITION BY</code>
        </p><p>
          If used, a <em class="replaceable"><code>partition_options</code></em> clause
          begins with <code class="literal">PARTITION BY</code>. This clause
          contains the function that is used to determine the partition;
          the function returns an integer value ranging from 1 to
          <em class="replaceable"><code>num</code></em>, where
          <em class="replaceable"><code>num</code></em> is the number of partitions.
          (The maximum number of user-defined partitions which a table
          may contain is 1024; the number of
          subpartitions—discussed later in this section—is
          included in this maximum.)
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            The expression (<em class="replaceable"><code>expr</code></em>) used in a
            <code class="literal">PARTITION BY</code> clause cannot refer to any
            columns not in the table being created; such references are
            specifically not permitted and cause the statement to fail
            with an error. (Bug #29444)
</p>
</div>
</li><li class="listitem"><p>
          <code class="literal">HASH(<em class="replaceable"><code>expr</code></em>)</code>
        </p><p>
          Hashes one or more columns to create a key for placing and
          locating rows. <em class="replaceable"><code>expr</code></em> is an
          expression using one or more table columns. This can be any
          valid MySQL expression (including MySQL functions) that yields
          a single integer value. For example, these are both valid
          <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements using
          <code class="literal">PARTITION BY HASH</code>:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE t1 (col1 INT, col2 CHAR(5))
    PARTITION BY HASH(col1);

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
    PARTITION BY HASH ( YEAR(col3) );</code></pre><p>
          You may not use either <code class="literal">VALUES LESS THAN</code> or
          <code class="literal">VALUES IN</code> clauses with <code class="literal">PARTITION
          BY HASH</code>.
        </p><p>
          <code class="literal">PARTITION BY HASH</code> uses the remainder of
          <em class="replaceable"><code>expr</code></em> divided by the number of
          partitions (that is, the modulus). For examples and additional
          information, see <a class="xref" href="partitioning-hash.html" title="23.2.4 HASH Partitioning">Section 23.2.4, “HASH Partitioning”</a>.
        </p><p>
          The <code class="literal">LINEAR</code> keyword entails a somewhat
          different algorithm. In this case, the number of the partition
          in which a row is stored is calculated as the result of one or
          more logical <a class="link" href="logical-operators.html#operator_and"><code class="literal">AND</code></a> operations. For
          discussion and examples of linear hashing, see
          <a class="xref" href="partitioning-linear-hash.html" title="23.2.4.1 LINEAR HASH Partitioning">Section 23.2.4.1, “LINEAR HASH Partitioning”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">KEY(<em class="replaceable"><code>column_list</code></em>)</code>
        </p><p>
          This is similar to <code class="literal">HASH</code>, except that MySQL
          supplies the hashing function so as to guarantee an even data
          distribution. The <em class="replaceable"><code>column_list</code></em>
          argument is simply a list of 1 or more table columns (maximum:
          16). This example shows a simple table partitioned by key,
          with 4 partitions:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY KEY(col3)
    PARTITIONS 4;</code></pre><p>
          For tables that are partitioned by key, you can employ linear
          partitioning by using the <code class="literal">LINEAR</code> keyword.
          This has the same effect as with tables that are partitioned
          by <code class="literal">HASH</code>. That is, the partition number is
          found using the
          <a class="link" href="bit-functions.html#operator_bitwise-and"><code class="literal">&amp;</code></a>
          operator rather than the modulus (see
          <a class="xref" href="partitioning-linear-hash.html" title="23.2.4.1 LINEAR HASH Partitioning">Section 23.2.4.1, “LINEAR HASH Partitioning”</a>, and
          <a class="xref" href="partitioning-key.html" title="23.2.5 KEY Partitioning">Section 23.2.5, “KEY Partitioning”</a>, for details). This example
          uses linear partitioning by key to distribute data between 5
          partitions:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR KEY(col3)
    PARTITIONS 5;</code></pre><p>
          The <code class="literal">ALGORITHM={1|2}</code> option is supported
          with <code class="literal">[SUB]PARTITION BY [LINEAR] KEY</code>.
          <code class="literal">ALGORITHM=1</code> causes the server to use the
          same key-hashing functions as MySQL 5.1;
          <code class="literal">ALGORITHM=2</code> means that the server employs
          the key-hashing functions implemented and used by default for
          new <code class="literal">KEY</code> partitioned tables in MySQL 5.5 and
          later. (Partitioned tables created with the key-hashing
          functions employed in MySQL 5.5 and later cannot be used by a
          MySQL 5.1 server.) Not specifying the option has the same
          effect as using <code class="literal">ALGORITHM=2</code>. This option is
          intended for use chiefly when upgrading or downgrading
          <code class="literal">[LINEAR] KEY</code> partitioned tables between
          MySQL 5.1 and later MySQL versions, or for creating tables
          partitioned by <code class="literal">KEY</code> or <code class="literal">LINEAR
          KEY</code> on a MySQL 5.5 or later server which can be used
          on a MySQL 5.1 server. For more information, see
          <a class="xref" href="alter-table-partition-operations.html" title="13.1.9.1 ALTER TABLE Partition Operations">Section 13.1.9.1, “ALTER TABLE Partition Operations”</a>.
        </p><p>
          <a class="link" href="mysqldump.html" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> in MySQL 5.7 (and later) writes
          this option encased in versioned comments, like this:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE t1 (a INT)
/*!50100 PARTITION BY KEY */ <em>/*!50611 ALGORITHM = 1 */</em> /*!50100 ()
      PARTITIONS 3 */</code></pre><p>
          This causes MySQL 5.6.10 and earlier servers to ignore the
          option, which would otherwise cause a syntax error in those
          versions. If you plan to load a dump made on a MySQL 5.7
          server where you use tables that are partitioned or
          subpartitioned by <code class="literal">KEY</code> into a MySQL 5.6
          server previous to version 5.6.11, be sure to consult
          <a class="ulink" href="https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html" target="_top">Changes in MySQL 5.6</a>,
          before proceeding. (The information found there also applies
          if you are loading a dump containing <code class="literal">KEY</code>
          partitioned or subpartitioned tables made from a MySQL
          5.7—actually 5.6.11 or later—server into a MySQL
          5.5.30 or earlier server.)
        </p><p>
          Also in MySQL 5.6.11 and later, <code class="literal">ALGORITHM=1</code>
          is shown when necessary in the output of
          <a class="link" href="show-create-table.html" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a> using
          versioned comments in the same manner as
          <a class="link" href="mysqldump.html" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>. <code class="literal">ALGORITHM=2</code>
          is always omitted from <code class="literal">SHOW CREATE TABLE</code>
          output, even if this option was specified when creating the
          original table.
        </p><p>
          You may not use either <code class="literal">VALUES LESS THAN</code> or
          <code class="literal">VALUES IN</code> clauses with <code class="literal">PARTITION
          BY KEY</code>.
        </p></li><li class="listitem"><p>
          <code class="literal">RANGE(<em class="replaceable"><code>expr</code></em>)</code>
        </p><p>
          In this case, <em class="replaceable"><code>expr</code></em> shows a range of
          values using a set of <code class="literal">VALUES LESS THAN</code>
          operators. When using range partitioning, you must define at
          least one partition using <code class="literal">VALUES LESS THAN</code>.
          You cannot use <code class="literal">VALUES IN</code> with range
          partitioning.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            For tables partitioned by <code class="literal">RANGE</code>,
            <code class="literal">VALUES LESS THAN</code> must be used with either
            an integer literal value or an expression that evaluates to
            a single integer value. In MySQL 8.0, you can
            overcome this limitation in a table that is defined using
            <code class="literal">PARTITION BY RANGE COLUMNS</code>, as described
            later in this section.
</p>
</div>
<p>
          Suppose that you have a table that you wish to partition on a
          column containing year values, according to the following
          scheme.
</p>
<div class="informaltable">
<table summary="A table partitioning scheme based on a column containing year values, as described in the preceding text. The table lists partition numbers and corresponding range of years."><col width="40%"><col width="60%"><thead><tr>
              <th scope="col">Partition Number:</th>
              <th scope="col">Years Range:</th>
            </tr></thead><tbody><tr>
              <td scope="row">0</td>
              <td>1990 and earlier</td>
            </tr><tr>
              <td scope="row">1</td>
              <td>1991 to 1994</td>
            </tr><tr>
              <td scope="row">2</td>
              <td>1995 to 1998</td>
            </tr><tr>
              <td scope="row">3</td>
              <td>1999 to 2002</td>
            </tr><tr>
              <td scope="row">4</td>
              <td>2003 to 2005</td>
            </tr><tr>
              <td scope="row">5</td>
              <td>2006 and later</td>
</tr></tbody></table>
</div>
<p>
          A table implementing such a partitioning scheme can be
          realized by the <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
          statement shown here:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE t1 (
    year_col  INT,
    some_data INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2002),
    PARTITION p4 VALUES LESS THAN (2006),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);</code></pre><p>
          <code class="literal">PARTITION ... VALUES LESS THAN ...</code>
          statements work in a consecutive fashion. <code class="literal">VALUES LESS
          THAN MAXVALUE</code> works to specify
          <span class="quote">“<span class="quote">leftover</span>”</span> values that are greater than the
          maximum value otherwise specified.
        </p><p>
          <code class="literal">VALUES LESS THAN</code> clauses work sequentially
          in a manner similar to that of the <code class="literal">case</code>
          portions of a <code class="literal">switch ... case</code> block (as
          found in many programming languages such as C, Java, and PHP).
          That is, the clauses must be arranged in such a way that the
          upper limit specified in each successive <code class="literal">VALUES LESS
          THAN</code> is greater than that of the previous one, with
          the one referencing <code class="literal">MAXVALUE</code> coming last of
          all in the list.
        </p></li><li class="listitem"><p>
          <code class="literal">RANGE
          COLUMNS(<em class="replaceable"><code>column_list</code></em>)</code>
        </p><p>
          This variant on <code class="literal">RANGE</code> facilitates partition
          pruning for queries using range conditions on multiple columns
          (that is, having conditions such as <code class="literal">WHERE a = 1 AND b
          &lt; 10</code> or <code class="literal">WHERE a = 1 AND b = 10 AND c
          &lt; 10</code>). It enables you to specify value ranges in
          multiple columns by using a list of columns in the
          <code class="literal">COLUMNS</code> clause and a set of column values
          in each <code class="literal">PARTITION ... VALUES LESS THAN
          (<em class="replaceable"><code>value_list</code></em>)</code> partition
          definition clause. (In the simplest case, this set consists of
          a single column.) The maximum number of columns that can be
          referenced in the <em class="replaceable"><code>column_list</code></em> and
          <em class="replaceable"><code>value_list</code></em> is 16.
        </p><p>
          The <em class="replaceable"><code>column_list</code></em> used in the
          <code class="literal">COLUMNS</code> clause may contain only names of
          columns; each column in the list must be one of the following
          MySQL data types: the integer types; the string types; and
          time or date column types. Columns using
          <code class="literal">BLOB</code>, <code class="literal">TEXT</code>,
          <code class="literal">SET</code>, <code class="literal">ENUM</code>,
          <code class="literal">BIT</code>, or spatial data types are not
          permitted; columns that use floating-point number types are
          also not permitted. You also may not use functions or
          arithmetic expressions in the <code class="literal">COLUMNS</code>
          clause.
        </p><p>
          The <code class="literal">VALUES LESS THAN</code> clause used in a
          partition definition must specify a literal value for each
          column that appears in the <code class="literal">COLUMNS()</code>
          clause; that is, the list of values used for each
          <code class="literal">VALUES LESS THAN</code> clause must contain the
          same number of values as there are columns listed in the
          <code class="literal">COLUMNS</code> clause. An attempt to use more or
          fewer values in a <code class="literal">VALUES LESS THAN</code> clause
          than there are in the <code class="literal">COLUMNS</code> clause causes
          the statement to fail with the error <span class="errortext">Inconsistency
          in usage of column lists for partitioning...</span>. You
          cannot use <code class="literal">NULL</code> for any value appearing in
          <code class="literal">VALUES LESS THAN</code>. It is possible to use
          <code class="literal">MAXVALUE</code> more than once for a given column
          other than the first, as shown in this example:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE rc (
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);</code></pre><p>
          Each value used in a <code class="literal">VALUES LESS THAN</code> value
          list must match the type of the corresponding column exactly;
          no conversion is made. For example, you cannot use the string
          <code class="literal">'1'</code> for a value that matches a column that
          uses an integer type (you must use the numeral
          <code class="literal">1</code> instead), nor can you use the numeral
          <code class="literal">1</code> for a value that matches a column that
          uses a string type (in such a case, you must use a quoted
          string: <code class="literal">'1'</code>).
        </p><p>
          For more information, see
          <a class="xref" href="partitioning-range.html" title="23.2.1 RANGE Partitioning">Section 23.2.1, “RANGE Partitioning”</a>, and
          <a class="xref" href="partitioning-pruning.html" title="23.4 Partition Pruning">Section 23.4, “Partition Pruning”</a>.
        </p></li><li class="listitem"><p>
          <code class="literal">LIST(<em class="replaceable"><code>expr</code></em>)</code>
        </p><p>
          This is useful when assigning partitions based on a table
          column with a restricted set of possible values, such as a
          state or country code. In such a case, all rows pertaining to
          a certain state or country can be assigned to a single
          partition, or a partition can be reserved for a certain set of
          states or countries. It is similar to
          <code class="literal">RANGE</code>, except that only <code class="literal">VALUES
          IN</code> may be used to specify permissible values for
          each partition.
        </p><p>
          <code class="literal">VALUES IN</code> is used with a list of values to
          be matched. For instance, you could create a partitioning
          scheme such as the following:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE client_firms (
    id   INT,
    name VARCHAR(35)
)
PARTITION BY LIST (id) (
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);</code></pre><p>
          When using list partitioning, you must define at least one
          partition using <code class="literal">VALUES IN</code>. You cannot use
          <code class="literal">VALUES LESS THAN</code> with <code class="literal">PARTITION BY
          LIST</code>.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            For tables partitioned by <code class="literal">LIST</code>, the value
            list used with <code class="literal">VALUES IN</code> must consist of
            integer values only. In MySQL 8.0, you can
            overcome this limitation using partitioning by <code class="literal">LIST
            COLUMNS</code>, which is described later in this section.
</p>
</div>
</li><li class="listitem"><p>
          <code class="literal">LIST
          COLUMNS(<em class="replaceable"><code>column_list</code></em>)</code>
        </p><p>
          This variant on <code class="literal">LIST</code> facilitates partition
          pruning for queries using comparison conditions on multiple
          columns (that is, having conditions such as <code class="literal">WHERE a =
          5 AND b = 5</code> or <code class="literal">WHERE a = 1 AND b = 10 AND c
          = 5</code>). It enables you to specify values in multiple
          columns by using a list of columns in the
          <code class="literal">COLUMNS</code> clause and a set of column values
          in each <code class="literal">PARTITION ... VALUES IN
          (<em class="replaceable"><code>value_list</code></em>)</code> partition
          definition clause.
        </p><p>
          The rules governing regarding data types for the column list
          used in <code class="literal">LIST
          COLUMNS(<em class="replaceable"><code>column_list</code></em>)</code> and
          the value list used in <code class="literal">VALUES
          IN(<em class="replaceable"><code>value_list</code></em>)</code> are the
          same as those for the column list used in <code class="literal">RANGE
          COLUMNS(<em class="replaceable"><code>column_list</code></em>)</code> and
          the value list used in <code class="literal">VALUES LESS
          THAN(<em class="replaceable"><code>value_list</code></em>)</code>,
          respectively, except that in the <code class="literal">VALUES IN</code>
          clause, <code class="literal">MAXVALUE</code> is not permitted, and you
          may use <code class="literal">NULL</code>.
        </p><p>
          There is one important difference between the list of values
          used for <code class="literal">VALUES IN</code> with <code class="literal">PARTITION
          BY LIST COLUMNS</code> as opposed to when it is used with
          <code class="literal">PARTITION BY LIST</code>. When used with
          <code class="literal">PARTITION BY LIST COLUMNS</code>, each element in
          the <code class="literal">VALUES IN</code> clause must be a
          <span class="emphasis"><em>set</em></span> of column values; the number of
          values in each set must be the same as the number of columns
          used in the <code class="literal">COLUMNS</code> clause, and the data
          types of these values must match those of the columns (and
          occur in the same order). In the simplest case, the set
          consists of a single column. The maximum number of columns
          that can be used in the <em class="replaceable"><code>column_list</code></em>
          and in the elements making up the
          <em class="replaceable"><code>value_list</code></em> is 16.
        </p><p>
          The table defined by the following <code class="literal">CREATE
          TABLE</code> statement provides an example of a table using
          <code class="literal">LIST COLUMNS</code> partitioning:
        </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE lc (
    a INT NULL,
    b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);</code></pre></li><li class="listitem"><p>
          <code class="literal">PARTITIONS <em class="replaceable"><code>num</code></em></code>
        </p><p>
          The number of partitions may optionally be specified with a
          <code class="literal">PARTITIONS <em class="replaceable"><code>num</code></em></code>
          clause, where <em class="replaceable"><code>num</code></em> is the number of
          partitions. If both this clause <span class="emphasis"><em>and</em></span> any
          <code class="literal">PARTITION</code> clauses are used,
          <em class="replaceable"><code>num</code></em> must be equal to the total
          number of any partitions that are declared using
          <code class="literal">PARTITION</code> clauses.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Whether or not you use a <code class="literal">PARTITIONS</code>
            clause in creating a table that is partitioned by
            <code class="literal">RANGE</code> or <code class="literal">LIST</code>, you
            must still include at least one <code class="literal">PARTITION
            VALUES</code> clause in the table definition (see below).
</p>
</div>
</li><li class="listitem"><p>
          <code class="literal">SUBPARTITION BY</code>
        </p><p>
          A partition may optionally be divided into a number of
          subpartitions. This can be indicated by using the optional
          <code class="literal">SUBPARTITION BY</code> clause. Subpartitioning may
          be done by <code class="literal">HASH</code> or <code class="literal">KEY</code>.
          Either of these may be <code class="literal">LINEAR</code>. These work
          in the same way as previously described for the equivalent
          partitioning types. (It is not possible to subpartition by
          <code class="literal">LIST</code> or <code class="literal">RANGE</code>.)
        </p><p>
          The number of subpartitions can be indicated using the
          <code class="literal">SUBPARTITIONS</code> keyword followed by an
          integer value.
        </p></li><li class="listitem"><p>
          Rigorous checking of the value used in
          <code class="literal">PARTITIONS</code> or
          <code class="literal">SUBPARTITIONS</code> clauses is applied and this
          value must adhere to the following rules:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              The value must be a positive, nonzero integer.
            </p></li><li class="listitem"><p>
              No leading zeros are permitted.
            </p></li><li class="listitem"><p>
              The value must be an integer literal, and cannot not be an
              expression. For example, <code class="literal">PARTITIONS
              0.2E+01</code> is not permitted, even though
              <code class="literal">0.2E+01</code> evaluates to
              <code class="literal">2</code>. (Bug #15890)
</p></li></ul>
</div>
</li><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>partition_definition</code></em></code>
        </p><p>
          Each partition may be individually defined using a
          <em class="replaceable"><code>partition_definition</code></em> clause. The
          individual parts making up this clause are as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              <code class="literal">PARTITION
              <em class="replaceable"><code>partition_name</code></em></code>
            </p><p>
              Specifies a logical name for the partition.
            </p></li><li class="listitem"><p>
              <code class="literal">VALUES</code>
            </p><p>
              For range partitioning, each partition must include a
              <code class="literal">VALUES LESS THAN</code> clause; for list
              partitioning, you must specify a <code class="literal">VALUES
              IN</code> clause for each partition. This is used to
              determine which rows are to be stored in this partition.
              See the discussions of partitioning types in
              <a class="xref" href="partitioning.html" title="Chapter 23 Partitioning">Chapter 23, <i>Partitioning</i></a>, for syntax examples.
            </p></li><li class="listitem"><p>
              <code class="literal">[STORAGE] ENGINE</code>
            </p><p>
              MySQL accepts a <code class="literal">[STORAGE] ENGINE</code> option
              for both <code class="literal">PARTITION</code> and
              <code class="literal">SUBPARTITION</code>. Currently, the only way
              in which this option can be used is to set all partitions
              or all subpartitions to the same storage engine, and an
              attempt to set different storage engines for partitions or
              subpartitions in the same table will give rise to the
              error <span class="errortext">ERROR 1469 (HY000): The mix of handlers
              in the partitions is not permitted in this version of
              MySQL</span>.
            </p></li><li class="listitem"><p>
              <code class="literal">COMMENT</code>
            </p><p>
              An optional <code class="literal">COMMENT</code> clause may be used
              to specify a string that describes the partition. Example:
            </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">COMMENT = 'Data for the years previous to 1999'</code></pre><p>
              The maximum length for a partition comment is 1024
              characters.
            </p></li><li class="listitem"><p>
              <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
              DIRECTORY</code>
            </p><p>
              <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
              DIRECTORY</code> may be used to indicate the directory
              where, respectively, the data and indexes for this
              partition are to be stored. Both the
              <code class="filename"><em class="replaceable"><code>data_dir</code></em></code>
              and the
              <code class="filename"><em class="replaceable"><code>index_dir</code></em></code>
              must be absolute system path names.
            </p><p>
              You must have the <a class="link" href="privileges-provided.html#priv_file"><code class="literal">FILE</code></a>
              privilege to use the <code class="literal">DATA DIRECTORY</code> or
              <code class="literal">INDEX DIRECTORY</code> partition option.
            </p><p>
              Example:
            </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/var/appdata/96/data'
    INDEX DIRECTORY = '/var/appdata/96/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/var/appdata/97/data'
    INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/var/appdata/98/data'
    INDEX DIRECTORY = '/var/appdata/98/idx'
);</code></pre><p>
              <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
              DIRECTORY</code> behave in the same way as in the
              <a class="link" href="create-table.html" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
              statement's <em class="replaceable"><code>table_option</code></em>
              clause as used for <code class="literal">MyISAM</code> tables.
            </p><p>
              One data directory and one index directory may be
              specified per partition. If left unspecified, the data and
              indexes are stored by default in the table's database
              directory.
            </p><p>
              The <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
              DIRECTORY</code> options are ignored for creating
              partitioned tables if
              <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create"><code class="literal">NO_DIR_IN_CREATE</code></a> is in
              effect.
            </p></li><li class="listitem"><p>
              <code class="literal">MAX_ROWS</code> and
              <code class="literal">MIN_ROWS</code>
            </p><p>
              May be used to specify, respectively, the maximum and
              minimum number of rows to be stored in the partition. The
              values for <em class="replaceable"><code>max_number_of_rows</code></em>
              and <em class="replaceable"><code>min_number_of_rows</code></em> must be
              positive integers. As with the table-level options with
              the same names, these act only as
              <span class="quote">“<span class="quote">suggestions</span>”</span> to the server and are not hard
              limits.
            </p></li><li class="listitem"><p>
              <code class="literal">TABLESPACE</code>
            </p><p>
              May be used to designate an <code class="literal">InnoDB</code>
              file-per-table tablespace for the partition by specifying
              <code class="literal">TABLESPACE `innodb_file_per_table`</code>. All
              partitions must belong to the same storage engine.
            </p><p>
              Placing <code class="literal">InnoDB</code> table partitions in
              shared <code class="literal">InnoDB</code> tablespaces is not
              supported. Shared tablespaces include the
              <code class="literal">InnoDB</code> system tablespace and general
              tablespaces.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          <code class="literal"><em class="replaceable"><code>subpartition_definition</code></em></code>
        </p><p>
          The partition definition may optionally contain one or more
          <em class="replaceable"><code>subpartition_definition</code></em> clauses.
          Each of these consists at a minimum of the
          <code class="literal">SUBPARTITION
          <em class="replaceable"><code>name</code></em></code>, where
          <em class="replaceable"><code>name</code></em> is an identifier for the
          subpartition. Except for the replacement of the
          <code class="literal">PARTITION</code> keyword with
          <code class="literal">SUBPARTITION</code>, the syntax for a subpartition
          definition is identical to that for a partition definition.
        </p><p>
          Subpartitioning must be done by <code class="literal">HASH</code> or
          <code class="literal">KEY</code>, and can be done only on
          <code class="literal">RANGE</code> or <code class="literal">LIST</code>
          partitions. See <a class="xref" href="partitioning-subpartitions.html" title="23.2.6 Subpartitioning">Section 23.2.6, “Subpartitioning”</a>.
</p></li></ul>
</div>
<p>
      <span class="bold"><strong>Partitioning by Generated Columns</strong></span>
    </p><p>
      Partitioning by generated columns is permitted. For example:
    </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE TABLE t1 (
  s1 INT,
  s2 INT AS (EXP(s1)) STORED
)
PARTITION BY LIST (s2) (
  PARTITION p1 VALUES IN (1)
);</code></pre><p>
      Partitioning sees a generated column as a regular column, which
      enables workarounds for limitations on functions that are not
      permitted for partitioning (see
      <a class="xref" href="partitioning-limitations-functions.html" title="23.6.3 Partitioning Limitations Relating to Functions">Section 23.6.3, “Partitioning Limitations Relating to Functions”</a>). The
      preceding example demonstrates this technique:
      <a class="link" href="mathematical-functions.html#function_exp"><code class="literal">EXP()</code></a> cannot be used directly in
      the <code class="literal">PARTITION BY</code> clause, but a generated column
      defined using <a class="link" href="mathematical-functions.html#function_exp"><code class="literal">EXP()</code></a> is permitted.
</p>
</div><br />
        </div>

                <div id="docs-in-page-nav-container">
            <div id="docs-in-page-nav">
                
    <a href="create-spatial-reference-system.html"
        aria-label="Previous" title="Previous: CREATE SPATIAL REFERENCE SYSTEM Syntax"><span
        class="icon-chevron-left"></span> PREV</a> &nbsp;
<a href="index.html" aria-label="Start" title="Start"> HOME</a> &nbsp;
        <a aria-label="Up" href="sql-syntax-data-definition.html" title="Up: Data Definition Statements"> UP</a> &nbsp;
    <a href="create-table-statement-retention.html" aria-label="Next"
        title="Next: CREATE TABLE Statement Retention">NEXT <span
        class="icon-chevron-right"></span></a>
            </div>
        </div>
        
         <div id="docs-body-extra">
             
<a class="docs-sidebar-section" href=""><span class="icon-related"></span>
    <span class="text">Related Documentation</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
        <a href="/doc/relnotes/mysql/8.0/en/">MySQL 8.0 Release Notes</a><br />
        <a href="/doc/dev/mysql-server/latest/">MySQL 8.0 Source Code Documentation</a><br />
        </div>
</div>

    <a class="docs-sidebar-section" href=""><span class="icon-download-thin"></span>
        <span class="text">
            Download
                            this Manual
                    </span>
    </a>
    <div class="docs-sidebar-accordian">
        <div class="text">
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.pdf">PDF (US Ltr)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.a4.pdf">PDF (A4)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-pdf-1-20190611.noarch.rpm">PDF (RPM)</a>
            - 41.5Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.tar.gz">HTML Download (TGZ)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.zip">HTML Download (Zip)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-html-chapter-1-20190611.noarch.rpm">HTML Download (RPM)</a>
            - 9.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.tar.gz">Man Pages (TGZ)</a>
            - 220.4Kb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.zip">Man Pages (Zip)</a>
            - 325.8Kb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.gz">Info (Gzip)</a>
            - 4.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.zip">Info (Zip)</a>
            - 4.1Mb<br />
                    </div>
    </div>

<a class="docs-sidebar-section" href=""><span class="icon-book"></span>
    <span class="text">Excerpts from this Manual</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
                <a href="/doc/mysql-backup-excerpt/8.0/en/">MySQL Backup and Recovery</a><br />
                <a href="/doc/mysql-g11n-excerpt/8.0/en/">MySQL Globalization</a><br />
                <a href="/doc/mysql-infoschema-excerpt/8.0/en/">MySQL Information Schema</a><br />
                <a href="/doc/mysql-installation-excerpt/8.0/en/">MySQL Installation Guide</a><br />
                <a href="/doc/mysql-security-excerpt/8.0/en/">Security in MySQL</a><br />
                <a href="/doc/mysql-startstop-excerpt/8.0/en/">Starting and Stopping MySQL</a><br />
                <a href="/doc/mysql-linuxunix-excerpt/8.0/en/">MySQL and Linux/Unix</a><br />
                <a href="/doc/mysql-windows-excerpt/8.0/en/">MySQL and Windows</a><br />
                <a href="/doc/mysql-osx-excerpt/8.0/en/">MySQL and OS X</a><br />
                <a href="/doc/mysql-solaris-excerpt/8.0/en/">MySQL and Solaris</a><br />
                <a href="/doc/mysql-sourcebuild-excerpt/8.0/en/">Building MySQL from Source</a><br />
                <a href="/doc/mysql-reslimits-excerpt/8.0/en/">MySQL Restrictions and Limitations</a><br />
                <a href="/doc/mysql-partitioning-excerpt/8.0/en/">MySQL Partitioning</a><br />
                <a href="/doc/mysql-secure-deployment-guide/8.0/en/">MySQL Secure Deployment Guide</a><br />
                <a href="/doc/mysql-tutorial-excerpt/8.0/en/">MySQL Tutorial</a><br />
                <a href="/doc/mysql-perfschema-excerpt/8.0/en/">MySQL Performance Schema</a><br />
                <a href="/doc/mysql-replication-excerpt/8.0/en/">MySQL Replication</a><br />
                <a href="/doc/mysql-repo-excerpt/8.0/en/">Using the MySQL Yum Repository</a><br />
            </div>
</div>
         </div>

              </div>
     </div>

</div>

<script>
$(function() {
    var doc = new $.doc({ 'mobile': $.browser.mobile, 'docId': 1, 'highlight': true });
});
</script>
    

            
        </div>
    </div>

    <footer class="collapsed">
        <div id="footer-bottom">
                                    <div id="footer-collapse">
                <a href="" id="expand-footer"
                    aria-label="Expand Footer"
                    title="Expand Footer"><span
                    class="icon-plus-square"></span></a>
            </div>
                        <div class="footer-contact">
                <div id="footer-contact-icon" style="display: none;">
                    <span class="icon-call-phone"></span>
                </div>
                <div id="footer-contact-numbers" style="display: none;">
                    <strong>Contact MySQL Sales</strong><br/>
                    USA/Canada: +1-866-221-0634 &nbsp;
                    (<a href="https://www.mysql.com/about/contact/phone/">More Countries &raquo;</a>)
                </div>
                <div id="footer-contact-copyright" style="display: inline-block;">
                    <a href="http://www.oracle.com/" aria-label="Oracle" title="Oracle"><span class="oracle-logo"></span></a>
                    &nbsp; &copy; 2019, Oracle Corporation and/or its affiliates
                </div>
            </div>
            <div class="social-icons">
                <a aria-label="Join us on Facebook" title="Join us on Facebook" href="http://www.facebook.com/mysql"><span class="icon-facebook-square"></span></a>
                <a aria-label="Follow us on Twitter" title="Follow us on Twitter" href="https://twitter.com/mysql"><span class="icon-twitter-square"></span></a>
                <a aria-label="Follow us on LinkedIn" title="Follow us on LinkedIn" href="https://www.linkedin.com/company/mysql"><span class="icon-linkedin-square"></span></a>
                <a aria-label="Visit our YouTube channel" title="Visit our YouTube channel" href="http://www.youtube.com/mysqlchannel"><span class="icon-youtube-square"></span></a>
            </div>
            <br class="clear" />
        </div>
        <div id="footer-links">

            <div id="footer-nav"></div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/products/">Products</a></li>
                    <li><a href="https://www.mysql.com/cloud/">Oracle MySQL Cloud Service</a></li>
                    <li><a href="https://www.mysql.com/products/enterprise/">MySQL Enterprise Edition</a></li>
                    <li><a href="https://www.mysql.com/products/standard/">MySQL Standard Edition</a></li>
                    <li><a href="https://www.mysql.com/products/classic/">MySQL Classic Edition</a></li>
                    <li><a href="https://www.mysql.com/products/cluster/">MySQL Cluster CGE</a></li>
                    <li><a href="https://www.mysql.com/oem/">MySQL Embedded (OEM/ISV)</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/services/">Services</a></li>
                    <li><a href="https://www.mysql.com/training/">Training</a></li>
                    <li><a href="https://www.mysql.com/certification/">Certification</a></li>
                    <li><a href="https://www.mysql.com/consulting/">Consulting</a></li>
                    <li><a href="https://www.mysql.com/support/">Support</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://dev.mysql.com/downloads/">Downloads</a></li>
                    <li><a href="https://dev.mysql.com/downloads/mysql/">MySQL Community Server</a></li>
                    <li><a href="https://dev.mysql.com/downloads/cluster/">MySQL NDB Cluster</a></li>
                    <li><a href="https://dev.mysql.com/downloads/shell/">MySQL Shell</a></li>
                    <li><a href="https://dev.mysql.com/downloads/router/">MySQL Router</a></li>
                    <li><a href="https://dev.mysql.com/downloads/workbench/">MySQL Workbench</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://dev.mysql.com/doc/">Documentation</a></li>
                    <li><a href="https://dev.mysql.com/doc/refman/en/">MySQL Reference Manual</a></li>
                    <li><a href="https://dev.mysql.com/doc/workbench/en/">MySQL Workbench</a></li>
                    <li><a href="https://dev.mysql.com/doc/index-cluster.html">MySQL NDB Cluster</a></li>
                    <li><a href="https://dev.mysql.com/doc/index-connectors.html">MySQL Connectors</a></li>
                    <li><a href="https://dev.mysql.com/doc/#topic">Topic Guides</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/about/">About MySQL</a></li>
                    <li><a href="https://www.mysql.com/about/contact/">Contact Us</a></li>
                                                            <li><a href="https://www.mysql.com/buy-mysql/">How to Buy</a></li>
                    <li><a href="https://www.mysql.com/partners/">Partners</a></li>
                    <li><a href="https://www.mysql.com/about/jobs/">Job Opportunities</a></li>
                    <li><a href="https://www.mysql.com/sitemap.html">Site Map</a></li>
                </ul>
            </div>

            
            <div id="footer-logo">
                <a href="http://www.oracle.com/" aria-label="Oracle" title="Oracle"><span class="oracle-logo"></span></a>
                &nbsp; &copy; 2019, Oracle Corporation and/or its affiliates
                <div class="footer-legal-links">
                    <a href="https://www.mysql.com/about/legal/">Legal Policies</a> |
                    <a href="http://www.oracle.com/us/legal/privacy/index.htm">Your Privacy Rights</a> |
                    <a href="http://www.oracle.com/us/legal/terms/index.html">Terms of Use</a> |
                    <a href="http://www.oracle.com/us/legal/third-party-trademarks/index.html">Trademark Policy</a> |
                    <a href="http://www.oracle.com/technetwork/community/oca-486395.html">Contributor Agreement</a> |
                    <div id="teconsent" style="display: inline-block"><script async="async" type="text/javascript" src="js/notice.js" crossorigin=""></script></div>
                </div>
            </div>

        </div>
    </footer>
</div>

        <script src="js/s_code_remote.js"></script>

        
    
</body>
</html>
